Date and Time Handling

Note

Since 4.0 the recommended way of working with date/time types is the NodaTime plugin.

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

Warning

A common mistake is for users to think that the PostgreSQL timestamp with timezone type stores the timezone in the database. This is not the case: only the timestamp is stored. There is no single PostgreSQL type that stores both a date/time and a timezone, similar to .NET DateTimeOffset.

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	Npgsql .NET Provider-Specific Type
timestamp without time zone	1 microsecond, 4713BC-294276AD	DateTime	100 nanoseconds, 1AD-9999AD	NpgsqlDateTime
timestamp with time zone	1 microsecond, 4713BC-294276AD	DateTime	100 nanoseconds, 1AD-9999AD	NpgsqlDateTime
date	1 day, 4713BC-5874897AD	DateTime	100 nanoseconds, 1AD-9999AD	NpgsqlDate
time without time zone	1 microsecond, 0-24 hours	TimeSpan	100 nanoseconds, -10,675,199 - 10,675,199 days	N/A
time with time zone	1 microsecond, 0-24 hours	DateTimeOffset (ignore date)	100 nanoseconds, 1AD-9999AD	N/A
interval	1 microsecond, -178000000-178000000 years	TimeSpan	100 nanoseconds, -10,675,199 - 10,675,199 days	NpgsqlTimeSpan

If your needs are met by the .NET native types, it is best that you use them directly with Npgsql. If, however, you require the extended range of a PostgreSQL type you can use Npgsql's provider-specific types, which represent PostgreSQL types in an exact way.

Timezones

It's critical to understand exactly how timezones and timezone conversions are handled between .NET types and PostgreSQL. In particular, .NET's DateTime has a Kind property which impacts how Npgsql reads and writes the value.

By default, DateTime is sent to PostgreSQL as a timestamp without time zone - no timezone conversion of any kind will occur, and your DateTime instance will be transferred as-is to PostgreSQL. This is the recommended way to store timestamps in the database. Note that you may still send DateTime as timestamp with time zone by setting NpgsqlDbType.TimestampTz on your NpgsqlParameter; in this case, if the Kind is Local, Npgsql will convert the value to UTC before sending it to PostgreSQL. Otherwise, it will be sent as-is.

You can also send DateTimeOffset values, which are written as timestamptz and are converted to UTC before sending.

PostgreSQL time with time zone is the only date/time type which actually stores a timezone in the database. You can use a DateTimeOffset to send one to PostgreSQL, in which case the date component is dropped and the time and timezone are preserved. You can also send a DateTime, in which case the Kind will determine the the timezone sent to the database.

Detailed Behavior: Sending values to the database

.NET value	NpgsqlDbType	Action
DateTime	NpgsqlDbType.Timestamp (default)	Send as-is
DateTime(Kind=UTC,Unspecified)	NpgsqlDbType.TimestampTz	Send as-is
DateTime(Kind=Local)	NpgsqlDbType.TimestampTz	Convert to UTC locally before sending

DateTimeOffset	NpgsqlDbType.TimestampTz (default)	Convert to UTC locally before sending

TimeSpan	NpgsqlDbType.Time (default)	Send as-is

DateTimeOffset	NpgsqlDbType.TimeTz	Send time and timezone
DateTime(Kind=UTC)	NpgsqlDbType.TimeTz	Send time and UTC timezone
DateTime(Kind=Local)	NpgsqlDbType.TimeTz	Send time and local system timezone
DateTime(Kind=Unspecified)	NpgsqlDbType.TimeTz	Assume local, send time and local system timezone

Detailed Behavior: Reading values from the database

PG type	.NET value	Action
timestamp	DateTime (default)	Kind=Unspecified

timestamptz	DateTime (default)	Kind=Local (according to system timezone)
timestamptz	DateTimeOffset	In local timezone offset

time	TimeSpan (default)	As-is

timetz	DateTimeOffset (default)	Date component is empty
timetz	TimeSpan	Strip offset, read as-is
timetz	DateTime	Strip offset, date is empty