Search Results for

    Show / Hide 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, 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).

    • Improve this Doc
    In This Article
    Back to top © Copyright 2022 The Npgsql Development Team