Table of Contents

Date/Time Mapping with NodaTime

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, and OffsetTime. These cleanly correspond to PostgreSQL date, time and timetz.
  • 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 set up the NodaTime plugin, add the Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime nuget to your project. Then, configure the NodaTime plugin as follows:

If you're passing a connection string to UseNpgsql, simply add the UseNodaTime call as follows:

builder.Services.AddDbContext<MyContext>(options => options.UseNpgsql(
    "<connection string>",
    o => o.UseNodaTime()));

This configures all aspects of Npgsql to use the NodaTime plugin - both at the EF and the lower-level Npgsql layer.

The above sets up all the necessary mappings and operation translators. You can now use NodaTime types as regular properties in your entities, and even perform some operations:

public class Post
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Instant CreationTime { get; set; }
}

var recentPosts = context.Posts.Where(p => p.CreationTime > someInstant);

Operation translation

The provider knows how to translate many members and methods on mapped NodaTime types. For example, the following query will be translated to SQL and evaluated server-side:

// Get all events which occurred on a Monday
var mondayEvents = context.Events.Where(p => p.SomeDate.DayOfWeek == DayOfWeek.Monday);

// Get all events which occurred before the year 2000
var oldEvents = context.Events.Where(p => p.SomeDate.Year < 2000);

Following is the list of supported NodaTime translations; If an operation you need is missing, please open an issue to request for it.

Note

Most translations on ZonedDateTime and Period were added in version 6.0

.NET SQL Notes
SystemClock.Instance.GetCurrentInstant() now()
LocalDateTime.Date date_trunc('day', timestamp)
LocalDateTime.Second (also LocalTime, ZonedDateTime) date_part('second', timestamp)::INT
LocalDateTime.Minute (also LocalTime, ZonedDateTime) date_part('minute', timestamp)::INT
LocalDateTime.Hour (also LocalTime, ZonedDateTime) date_part('hour', timestamp)::INT
LocalDateTime.Day, (also LocalDate, ZonedDateTime) date_part('day', timestamp)::INT
LocalDateTime.Month (also LocalDate, ZonedDateTime) date_part('month', timestamp)::INT
LocalDateTime.Year (also LocalDate, ZonedDateTime) date_part('year', timestamp)::INT
LocalDateTime.DayOfWeek (also LocalDate, ZonedDateTime) floor(date_part('dow', timestamp))::INT
LocalDateTime.DayOfYear (also LocalDate, ZonedDateTime) date_part('doy', timestamp)::INT
Period.Seconds (also Duration) date_part('second', interval)::INT
Period.Minutes (also Duration) date_part('minute', interval)::INT
Period.Hours (also Duration) date_part('hour', interval)::INT
Period.Days (also Duration) date_part('day', interval)::INT
Period.Months date_part('month', interval)::INT
Period.Years date_part('year', interval)::INT
Period.FromSeconds make_interval(seconds => int)
Period.FromMinutes make_interval(minutes => int)
Period.FromHours make_interval(hours => int)
Period.FromDays make_interval(days => int)
Period.FromWeeks make_interval(weeks => int)
Period.FromMonths make_interval(months => int)
Period.FromYears make_interval(years => int)
Duration.TotalMilliseconds date_part('epoch', interval) / 0.001
Duration.TotalSeconds date_part('epoch', interval)
Duration.TotalMinutes date_part('epoch', interval) / 60.0
Duration.TotalDays date_part('epoch', interval) / 86400.0
Duration.TotalHours date_part('epoch', interval) / 3600.0
ZonedDateTime.LocalDateTime timestamptz AT TIME ZONE 'UTC' Added in 6.0
DateInterval.Length upper(daterange) - lower(daterange) Added in 6.0
DateInterval.Start lower(daterange) Added in 6.0
DateInterval.End upper(daterange) - INTERVAL 'P1D' Added in 6.0
DateInterval.Contains(LocalDate) daterange @> date Added in 6.0
DateInterval.Contains(DateInterval) daterange @> daterange Added in 6.0
DateInterval.Intersection(DateInterval) daterange * daterange Added in 6.0
DateInterval.Union(DateInterval) daterange + daterange Added in 6.0
Instant.InZone(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).LocalDateTime timestamptz AT TIME ZONE 'Europe/Berlin' Added in 6.0
LocalDateTime.InZoneLeniently(DateTimeZoneProviders.Tzdb["Europe/Berlin"]).ToInstant() timestamp AT TIME ZONE 'Europe/Berlin' Added in 6.0
ZonedDateTime.ToInstant No PG operation (.NET-side conversion from ZonedDateTime to Instant only) Added in 6.0
Instant.InUtc No PG operation (.NET-side conversion from Instant to ZonedDateTime only) Added in 6.0
Instant.ToDateTimeUtc No PG operation (.NET-side conversion from Instant to UTC DateTime only) Added in 6.0
EF.Functions.Sum(periods) sum(periods) Added in 7.0, see Aggregate functions.
EF.Functions.Sum(durations) sum(durations) Added in 7.0, see Aggregate functions.
EF.Functions.Average(periods) avg(durations) Added in 7.0, see Aggregate functions.
EF.Functions.Average(durations) avg(durations) Added in 7.0, see Aggregate functions.

In addition to the above, most arithmetic operators are also translated (e.g. LocalDate + Period).

Edit this page