Date and Time Handling

This page documents how the Npgsql Entity Framework Core provider handles date and time types when mapping between .NET and PostgreSQL, and how it translates LINQ queries involving date/time operations to SQL.

Overview

Npgsql supports mapping between .NET date/time types (DateTime, DateTimeOffset, DateOnly, TimeOnly, TimeSpan) and their PostgreSQL counterparts (timestamp, timestamptz, date, time, interval). The provider includes specialized translators that convert LINQ expressions using these types into PostgreSQL-specific SQL.

Mapping Relationships

Sources: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs44-50 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs30-34

Type Mapping System

The mapping of .NET DateTime to PostgreSQL types depends on the configuration and the specific RelationalTypeMapping instance resolved by the provider.

DateTime and DateTimeOffset

• timestamp without time zone: Handled by NpgsqlTimestampTypeMapping src/EFCore.PG/Storage/Internal/Mapping/NpgsqlTimestampTypeMapping.cs13 By default, DateTime maps here src/EFCore.PG/Storage/Internal/Mapping/NpgsqlTimestampTypeMapping.cs29-32
• timestamp with time zone: Handled by NpgsqlTimestampTzTypeMapping src/EFCore.PG/Storage/Internal/Mapping/NpgsqlTimestampTzTypeMapping.cs13 Both DateTime and DateTimeOffset can map here src/EFCore.PG/Storage/Internal/Mapping/NpgsqlTimestampTzTypeMapping.cs34-37

Legacy Timestamp Behavior

Npgsql supports a legacy mode that changes how timestamps are handled, primarily controlled by NpgsqlTypeMappingSource.LegacyTimestampBehavior src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs75

Sources: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs105-117 src/EFCore.PG/Storage/Internal/Mapping/NpgsqlTimestampTzTypeMapping.cs119-127

LINQ Translation

The provider uses NpgsqlDateTimeMemberTranslator and NpgsqlDateTimeMethodTranslator to convert .NET operations into PostgreSQL functions like date_part, date_trunc, and AT TIME ZONE.

Member Translation

The NpgsqlDateTimeMemberTranslator.Translate method handles property access src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs36-41

.NET Member	PostgreSQL Translation	Logic Source
DateTime.Now	now() (with local conversion)	src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs113-115
DateTime.UtcNow	now()	src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs116
DateTime.Today	date_trunc('day', now())	src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs118-124
DateTime.Year	date_part('year', ...)	src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs126
DateTime.DayOfWeek	date_part('dow', ...)	src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs137

Method Translation

NpgsqlDateTimeMethodTranslator handles instance and static method calls src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs12-16

• Arithmetic: AddYears, AddMonths, AddDays, etc., are translated using PostgreSQL INTERVAL addition src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs47-60
• Conversions: ToUniversalTime() and ToLocalTime() use SQL CAST or type conversion to switch between timestamp and timestamptz src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs109-118
• Special Functions: DateOnly.FromDateTime is translated to a cast to date, with an AT TIME ZONE 'UTC' shift if the source is a timestamptz src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs130-144

Time Zone Handling

The provider provides deep integration with PostgreSQL's time zone capabilities. This is primarily implemented via NpgsqlSqlExpressionFactory.AtTimeZone and AtUtc helpers.

Translation Logic for TimeZones

Sources: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs84-89 src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs337-368

NodaTime Integration

For users requiring advanced date/time logic, the EFCore.PG.NodaTime plugin maps NodaTime types directly to PostgreSQL types. This is managed by NpgsqlNodaTimeMemberTranslatorPlugin src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs12-16 and NpgsqlNodaTimeMethodCallTranslatorPlugin src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs14-15

NodaTime Type Mapping

NodaTime Type	PostgreSQL Type	Member Translator
Instant	timestamptz	src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs89-134
LocalDateTime	timestamp	src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs79-85
LocalDate	date	src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs49
Period	interval	src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs50

Advanced NodaTime Translations

• ZonedDateTime: Uses PendingZonedDateTimeExpression to track the time zone during translation until it can be converted to a PostgreSQL AT TIME ZONE operation src/EFCore.PG.NodaTime/Query/Internal/PendingZonedDateTimeExpression.cs1-10
• Intervals: Maps Interval to tsrange and DateInterval to daterange, utilizing PostgreSQL range functions like lower(), upper(), lower_inf(), and upper_inf() src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs131-155

Sources: src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMemberTranslatorPlugin.cs12-28 src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs59-72

Distance Operations

The provider supports the PostgreSQL distance operator <-> for temporal types via EF.Functions.Distance. This allows for "closest to" queries that can be optimized by GiST indexes.

• DateTime: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs99-105
• DateOnly: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs147-153
• NodaTime Instant: src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs130-132

Sources: src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMethodTranslator.cs104 src/EFCore.PG.NodaTime/Query/Internal/NpgsqlNodaTimeMethodCallTranslatorPlugin.cs132