NodaTime Type Plugin
Since 4.0, Npgsql supports type plugins, which are external nuget packages that modify how Npgsql maps PostgreSQL values to CLR types. One of these is the NodaTime plugin, which makes Npgsql read and write NodaTime types. The NodaTime plugin is now the recommended way to interact with PostgreSQL date/time types, and isn't the default only because of the added dependency on the NodaTime library.
What is NodaTime
By default, the PostgreSQL date/time types are mapped to the built-in .NET types (DateTime
, TimeSpan
). Unfortunately, these built-in types are flawed in many ways. The NodaTime library was created to solve many of these problems, and if your application handles dates and times in anything but the most basic way, you should consider using it. To learn more read this blog post by Jon Skeet.
Beyond NodaTime's general advantages, some specific advantages NodaTime for PostgreSQL date/time mapping include:
- NodaTime defines some types which are missing from the BCL, such as
LocalDate
,LocalTime
, andOffsetTime
. These cleanly correspond to PostgreSQLdate
,time
andtimetz
. Period
is much more suitable for mapping PostgreSQLinterval
thanTimeSpan
.- NodaTime types can fully represent PostgreSQL's microsecond precision, and can represent dates outside the BCL's date limit (1AD-9999AD).
Setup
To use the NodaTime plugin, simply add a dependency on Npgsql.NodaTime and set it up:
using Npgsql;
// Place this at the beginning of your program to use NodaTime everywhere (recommended)
NpgsqlConnection.GlobalTypeMapper.UseNodaTime();
// Or to temporarily use NodaTime on a single connection only:
conn.TypeMapper.UseNodaTime();
Reading and Writing Values
Once the plugin is set up, you can transparently read and write NodaTime objects:
// Write NodaTime Instant to PostgreSQL "timestamp without time zone"
using (var cmd = new NpgsqlCommand(@"INSERT INTO mytable (my_timestamp) VALUES (@p)", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("p", Instant.FromUtc(2011, 1, 1, 10, 30)));
cmd.ExecuteNonQuery();
}
// Read timestamp back from the database as an Instant
using (var cmd = new NpgsqlCommand(@"SELECT my_timestamp FROM mytable", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
var instant = reader.GetFieldValue<Instant>(0);
}
Mapping Table
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 t
imestamp is stored. There is no single PostgreSQL type that stores both a date/time and a timezone, similar to .NET DateTimeOffset.
PostgreSQL Type | Default NodaTime Type | Additional NodaTime Type | Notes |
---|---|---|---|
timestamp without time zone | Instant | LocalDateTime | It's common to store UTC timestamps in databases - you can simply do so and read/write Instant values. You also have the option of readin/writing LocalDateTime, which is a date/time with no information about timezones; this makes sense if you're storing the timezone in a different column and want to read both into a NodaTime ZonedDateTime. |
timestamp with time zone | Instant | ZonedDateTime, OffsetDateTime | This PostgreSQL type stores only a timestamp, assumed to be in UTC. If you read/write this as an Instant, it will be provided as stored with no timezone conversions whatsoever. If, however, you read/write as a ZonedDateTime or OffsetDateTime, the plugin will automatically convert to and from UTC according to your PostgreSQL session's timezone. |
date | LocalDate | A simple date with no timezone or offset information. | |
time without time zone | LocalTime | A simple time-of-day, with no timezone or offset information. | |
time with time zone | OffsetTime | This is a PostgreSQL type that stores a time and an offset. | |
interval | Period | Duration | Represents an interval of time, from sub-second units to years. NodaTime Duration is supported for intervals with days and smaller, but not with years or months (as these have no absolute duration). Period can be used with any interval unit. |
Additional Notes
- The plugin automatically converts
timestamp with time zone
to and from your PostgreSQL session's configured timezone; this is unlike Npgsql's default mapping which uses your machine's local timezone instead. The NodaTime plugin behavior matches the regular PostgreSQL behavior when interacting withtimestamptz
values. - To read and write
timestamp
ordate
infinity values, set theConvert Infinity DateTime
connection string parameter to true and read/write MaxValue/MinValue.