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, andOffsetTime. These cleanly correspond to PostgreSQLdate,timeandtimetz. Periodis much more suitable for mapping PostgreSQLintervalthanTimeSpan.- 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, make the following modification to your UseNpgsql() line:
protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
builder.UseNpgsql("Host=localhost;Database=test;Username=npgsql_tests;Password=npgsql_tests",
o => o.UseNodaTime());
}
This will set 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 |
In addition to the above, most arithmetic operators are also translated (e.g. LocalDate + Period).