Search Results for

    Show / Hide Table of Contents

    Date and Time Handling

    Warning

    Npgsql 6.0 introduced some important changes to how timestamps are mapped, see the release notes for more information.

    Note

    The recommended way of working with date/time types is the NodaTime plugin: the NodaTime types are much better-designed, avoid the flaws in the built-in BCL types, and are fully supported by Npgsql.

    Handling date and time values usually isn't hard, but you must pay careful attention to differences in how the .NET types and PostgreSQL represent dates. It's worth reading the PostgreSQL date/time type documentation to familiarize yourself with PostgreSQL's types.

    .NET types and PostgreSQL types

    The .NET and PostgreSQL types differ in the resolution and range they provide; the .NET type usually have a higher resolution but a lower range than the PostgreSQL types:

    PostgreSQL type Precision/Range .NET Native Type Precision/Range
    timestamp with time zone 1 microsecond, 4713BC-294276AD DateTime (UTC) 100 nanoseconds, 1AD-9999AD
    timestamp without time zone 1 microsecond, 4713BC-294276AD DateTime (Unspecified) 100 nanoseconds, 1AD-9999AD
    date 1 day, 4713BC-5874897AD DateOnly (6.0+), DateTime 100 nanoseconds, 1AD-9999AD
    time without time zone 1 microsecond, 0-24 hours TimeOnly (6.0+), TimeSpan 100 nanoseconds, -10,675,199 - 10,675,199 days
    time with time zone 1 microsecond, 0-24 hours DateTimeOffset (ignore date) 100 nanoseconds, 1AD-9999AD
    interval 1 microsecond, -178000000-178000000 years TimeSpan 100 nanoseconds, -10,675,199 - 10,675,199 days

    For almost all applications, the range of the .NET native types (or the NodaTime types) are more than sufficient. In the rare cases where you need to access values outside these ranges, timestamps can be accessed as long, dates as int, and intervals as NpgsqlInterval. These are the raw PostgreSQL binary representations of these type, so you'll have to deal with encoding/decoding yourself.

    Timestamps and timezones

    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.

    In PostgreSQL, timestamp with time zone represents a UTC timestamp, while timestamp without time zone represents a local or unspecified time zone. Starting with 6.0, Npgsql maps UTC DateTime to timestamp with time zone, and Local/Unspecified DateTime to timestamp without time zone; trying to send a non-UTC DateTime as timestamptz will throw an exception, etc. Npgsql also supports reading and writing DateTimeOffset to timestamp with time zone, but only with Offset=0. Prior to 6.0, timestamp with time zone would be converted to a local timestamp when read - see below for more details. The precise improvements and breaking changes are detailed in the 6.0 breaking changes; to revert to the pre-6.0 behavior, add the following at the start of your application, before any Npgsql operations are invoked:

    AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
    

    Use of the time with time zone type is discouraged, see the PostgreSQL documentation. You can use a DateTimeOffset to read and write values - the date component will be ignored.

    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 (DateTime and DateOnly, NodaTime 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.

    Detailed Behavior: Reading values from the database

    PostgreSQL type Default .NET type Non-default .NET types
    timestamp with time zone DateTime (Utc1) DateTimeOffset (Offset=0)2
    timestamp without time zone DateTime (Unspecified)
    date DateTime DateOnly (6.0+)
    time without time zone TimeSpan TimeOnly (6.0+)
    time with time zone DateTimeOffset
    interval TimeSpan (3) NpgsqlInterval

    1 In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), reading a timestamp with time zone returns a Local DateTime instead of Utc. See the breaking change note for more info.

    2 In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), reading a timestamp with time zone as a DateTimeOffset returns a local offset based on the timezone of the server where Npgsql is running.

    3 PostgreSQL intervals with month or year components cannot be read as TimeSpan. Consider using NodaTime's Period type, or NpgsqlInterval.

    Detailed Behavior: Sending values to the database

    PostgreSQL type Default .NET types Non-default .NET types NpgsqlDbType DbType
    timestamp with time zone DateTime (Utc)1, DateTimeOffset TimestampTz DateTime2, DateTimeOffset
    timestamp without time zone DateTime (Local/Unspecified)1 Timestamp DateTime2
    date DateOnly (6.0+) DateTime Date Date
    time without time zone TimeOnly (6.0+) TimeSpan Time Time
    time with time zone DateTimeOffset TimeTz
    interval TimeSpan Interval

    1 UTC DateTime is written as timestamp with time zone, Local/Unspecified DateTimes are written as timestamp without time zone. In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DateTime is always written as timestamp without time zone.

    2In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DbType.DateTime is mapped to timestamp without time zone.

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