NodaTime Type Plugin

Npgsql provides a plugin that allows mapping the NodaTime date/time library; this is the recommended way to interact with PostgreSQL date/time types, rather than the built-in .NET types.

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's types map very cleanly to the PostgreSQL types. For example Instant corresponds to timestamptz, and LocalDateTime corresponds to timestamp without time zone. The BCL's DateTime can correspond to both, depending on its type; this can create confusion and errors.
  • Period is much more suitable for mapping PostgreSQL interval than TimeSpan.
  • 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 with time zone" (UTC)
using (var cmd = new NpgsqlCommand(@"INSERT INTO mytable (my_timestamptz) 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_timestamptz 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 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.

PostgreSQL Type Default NodaTime Type Additional NodaTime Type Notes
timestamp with time zone Instant ZonedDateTime1, OffsetDateTime1 A UTC timestamp in the database. Only UTC ZonedDateTime and OffsetDateTime are supported.
timestamp without time zone LocalDateTime2 A timestamp in an unknown or implicit time zone.
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 A type that stores a time and an offset. It's use is generally discouraged.
interval Period Duration 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.
tstzrange Interval NpgsqlRange<Instant> etc. An interval between two instants in time (start and end).
tsrange NpgsqlRange<LocalDateTime> An interval between two timestamps in an unknown or implicit time zone.
daterange DateInterval NpgsqlRange<LocalDate> etc. An interval between two dates.

1 In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), writing or reading ZonedDateTime or OffsetDateTime automatically converted to or from UTC. See the breaking change note for more info.

2 In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), timestamp without time zone was mapped to Instant by default, instead of LocalDateTime. See the breaking change note for more info.

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 (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.