Date and Time Handling

Warning

Npgsql 6.0 introduced some important changes to how timestamps are mapped, see the release notes for more information.

Note

The recommended way of working with date/time types is the NodaTime plugin: the NodaTime types are much better-designed, avoid the flaws in the built-in BCL types, and are fully supported by Npgsql.

Handling date and time values usually isn't hard, but you must pay careful attention to differences in how the .NET types and PostgreSQL represent dates. It's worth reading the PostgreSQL date/time type documentation to familiarize yourself with PostgreSQL's types.

.NET types and PostgreSQL types

The .NET and PostgreSQL types differ in the resolution and range they provide; the .NET type usually have a higher resolution but a lower range than the PostgreSQL types:

PostgreSQL type	Precision/Range	.NET Native Type	Precision/Range
timestamp with time zone	1 microsecond, 4713BC-294276AD	DateTime (UTC)	100 nanoseconds, 1AD-9999AD
timestamp without time zone	1 microsecond, 4713BC-294276AD	DateTime (Unspecified)	100 nanoseconds, 1AD-9999AD
date	1 day, 4713BC-5874897AD	DateOnly (6.0+), DateTime	100 nanoseconds, 1AD-9999AD
time without time zone	1 microsecond, 0-24 hours	TimeOnly (6.0+), TimeSpan	100 nanoseconds, -10,675,199 - 10,675,199 days
time with time zone	1 microsecond, 0-24 hours	DateTimeOffset (ignore date)	100 nanoseconds, 1AD-9999AD
interval	1 microsecond, -178000000-178000000 years	TimeSpan	100 nanoseconds, -10,675,199 - 10,675,199 days

For almost all applications, the range of the .NET native types (or the NodaTime types) are more than sufficient. In the rare cases where you need to access values outside these ranges, timestamps can be accessed as long, dates as int, and intervals as NpgsqlInterval. These are the raw PostgreSQL binary representations of these type, so you'll have to deal with encoding/decoding yourself.

Timestamps and timezones

Warning

A common mistake is for users to think that the PostgreSQL timestamp with time zone type stores the timezone in the database. This is not the case: only a UTC timestamp is stored. There is no single PostgreSQL type that stores both a date/time and a timezone, similar to .NET DateTimeOffset. To store a timezone in the database, add a separate text column containing the timezone ID.

In PostgreSQL, timestamp with time zone represents a UTC timestamp, while timestamp without time zone represents a local or unspecified time zone. Starting with 6.0, Npgsql maps UTC DateTime to timestamp with time zone, and Local/Unspecified DateTime to timestamp without time zone; trying to send a non-UTC DateTime as timestamptz will throw an exception, etc. Npgsql also supports reading and writing DateTimeOffset to timestamp with time zone, but only with Offset=0. Prior to 6.0, timestamp with time zone would be converted to a local timestamp when read - see below for more details. The precise improvements and breaking changes are detailed in the 6.0 breaking changes; to revert to the pre-6.0 behavior, add the following at the start of your application, before any Npgsql operations are invoked:

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

Use of the time with time zone type is discouraged, see the PostgreSQL documentation. You can use a DateTimeOffset to read and write values - the date component will be ignored.

Infinity values

PostgreSQL supports the special values -infinity and infinity for the timestamp and date types (see docs); these can be useful to represent a value which is earlier or later than any other value. Starting with Npgsql 6.0, these special values are mapped to the MinValue and MaxValue value on the corresponding .NET types (DateTime and DateOnly, NodaTime Instant and LocalDate). To opt out of this behavior, set the following AppContext switch at the start of your application:

AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);

Note: in versions prior to 6.0, the connection string parameter Convert Infinity DateTime could be used to opt into these infinity conversions. That connection string parameter has been removed.

Detailed Behavior: Reading values from the database

PostgreSQL type	Default .NET type	Non-default .NET types
timestamp with time zone	DateTime (Utc1)	DateTimeOffset (Offset=0)2
timestamp without time zone	DateTime (Unspecified)
date	DateTime	DateOnly (6.0+)
time without time zone	TimeSpan	TimeOnly (6.0+)
time with time zone	DateTimeOffset
interval	TimeSpan (3)	NpgsqlInterval

¹ In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), reading a timestamp with time zone returns a Local DateTime instead of Utc. See the breaking change note for more info.

² In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), reading a timestamp with time zone as a DateTimeOffset returns a local offset based on the timezone of the server where Npgsql is running.

³ PostgreSQL intervals with month or year components cannot be read as TimeSpan. Consider using NodaTime's Period type, or NpgsqlInterval.

Detailed Behavior: Sending values to the database

PostgreSQL type	Default .NET types	Non-default .NET types	NpgsqlDbType	DbType
timestamp with time zone	DateTime (Utc)1, DateTimeOffset		TimestampTz	DateTime2, DateTimeOffset
timestamp without time zone	DateTime (Local/Unspecified)1		Timestamp	DateTime2
date	DateOnly (6.0+)	DateTime	Date	Date
time without time zone	TimeOnly (6.0+)	TimeSpan	Time	Time
time with time zone		DateTimeOffset	TimeTz
interval	TimeSpan		Interval

¹ UTC DateTime is written as timestamp with time zone, Local/Unspecified DateTimes are written as timestamp without time zone. In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DateTime is always written as timestamp without time zone.

²In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DbType.DateTime is mapped to timestamp without time zone.