Date and Time Handling
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
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||1 microsecond, 4713BC-294276AD||DateTime||100 nanoseconds, 1AD-9999AD||NpgsqlDateTime|
|timestamp with timezone||1 microsecond, 4713BC-294276AD||DateTime||100 nanoseconds, 1AD-9999AD||NpgsqlDateTime|
|date||1 day, 4713BC-5874897AD||DateTime||100 nanoseconds, 1AD-9999AD||NpgsqlDate|
|time||1 microsecond, 0-24 hours||TimeSpan||100 nanoseconds, -10,675,199 - 10,675,199 days||N/A|
|time with timezone||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.
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.
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
timestamp with time zone by setting
NpgsqlDbType.TimestampTz on your
NpgsqlParameter; in this case, if the
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.
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
|DateTime||NpgsqlDbType.Timestamp (default)||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|
|timestamptz||DateTime (default)||Kind=Local (according to system timezone)|
|timestamptz||DateTimeOffset||In local timezone offset|
|timetz||DateTimeOffset (default)||Date component is empty|
|timetz||TimeSpan||Strip offset, read as-is|
|timetz||DateTime||Strip offset, date is empty|
If you're really interested in some of the mapping decisions above, check out this issue.