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 avoid forcing a dependency on the NetTopologySuite library for users not using spatial, NodaTime support is delivered as a separate plugin. To use the plugin, simply add a dependency on Npgsql.NodaTime and set it up in one of the following ways:

Note

NpgsqlDataSource was introduced in Npgsql 7.0, and is the recommended way to manage type mapping. If you're using an older version, see the other methods.

var dataSourceBuilder = new NpgsqlDataSourceBuilder(...);
dataSourceBuilder.UseNodaTime();
await using var dataSource = dataSourceBuilder.Build();

If you're using an older version of Npgsql which doesn't yet support NpgsqlDataSource, you can configure mappings globally for all connections in your application:

NpgsqlConnection.GlobalTypeMapper.UseNodaTime();

For this to work, you must place this code at the beginning of your application, before any other Npgsql API is called. Note that in Npgsql 7.0, global type mappings are obsolete (but still supported) - NpgsqlDataSource is the recommended way to manage type mappings.

Note

This mapping method has been removed in Npgsql 7.0.

Older versions of Npgsql supported configuring a type mapping on an individual connection, as follows:

var conn = new NpgsqlConnection(...);
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)
await using (var cmd = new NpgsqlCommand(@"INSERT INTO mytable (my_timestamptz) VALUES ($1)", conn))
{
    cmd.Parameters.Add(new() { Value = Instant.FromUtc(2011, 1, 1, 10, 30) });
    await cmd.ExecuteNonQueryAsync();
}

// Read timestamp back from the database as an Instant
await using (var cmd = new NpgsqlCommand(@"SELECT my_timestamptz FROM mytable", conn))
await using (var reader = await cmd.ExecuteReaderAsync())
{
    await reader.ReadAsync();
    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	ZonedDateTime¹, OffsetDateTime¹	A UTC timestamp in the database. Only UTC ZonedDateTime and OffsetDateTime are supported.
timestamp without time zone	LocalDateTime²		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.

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

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