How should I persist timestamps in SQL DB if app uses NodaTime?

Question

I want to start using NodaTime in my application to manage times, instants and general time localization.

Sometimes I persist timestamps to a SQL Server 2008 database. I had traditionally used datetime2 fields in UTC. These timestamps will be created using Noda. It seems that this date conversion to Noda's Instant may be undesirable.

What type should I use to persist them?

If I use a non-integer in SQL then I have potential conversion issues between my App layer and my DAL. However, if I persist the integer Noda instant I will have a logical coupling between the same layers... and I will not be able to perform simple date aggregations in SQL without bringing it into the app layer or CLR.

Noda makes the case that instants can't reliably be described in UTC, since certain times never occurred in UTC.

Answer 1

A datetime2 is a perfectly acceptable and normal SQL type to store an Instant. Use the Instant.ToDateTimeUtc method to get a DateTime, then store that in SQL as normal. Likewise, you can use the Instant.FromDateTimeUtc method when retrieving the value from SQL.

Alternatively, you could use a SQL datetimeoffset type if you wanted to be explicit that the values are UTC based (the offset will always be zero). There are ToDateTimeOffset and FromDateTimeOffset methods on Instant you can use.

You said:

Noda makes the case that instants can't reliably be described in UTC, since certain times never occurred in UTC.

I think perhaps you're caught up in the wording in the user guide. I can see how it might lead you down that line of thinking. While it's true that logically an Instant doesn't represent UTC, it certainly can be reliably described in terms of UTC. It could also be described in some other terms, as long as those terms were unambiguous.

The point that the user guide is making is that other values that aren't in UTC could still be converted to an Instant. For example, I might have an OffsetDateTime, or a DateTimeOffset that has a different offset than zero, and it still could be adjusted back to zero to form an Instant. Likewise, I might have a ZonedDateTime that's assigned to the UTC time zone, or some other time zone, I can still get back to a single universal Instant without loss of fidelity.

The same cannot be said of DateTime (unless it has DateTimeKind.Utc), or of LocalDateTime, LocalDate, LocalTime, etc. None of those are unambiguously a single instant in time.

As far as the other mappings:

Noda Time      | .NET BCL                   | SQL Server ---------------|----------------------------|------------------------------------------ Instant        | DateTime or DateTimeOffset | datetime2 or datetimeoffset OffsetDateTime | DateTimeOffset             | datetimeoffset LocalDateTime  | DateTime                   | datetime2 LocalDate      | DateTime                   | date LocalTime      | TimeSpan                   | time Duration       | TimeSpan                   | int or bigint (Ticks, TotalSeconds, etc.) Period         | String                     | varchar ZonedDateTime  | DateTimeOffset + String    | datetimeoffset + varchar (or a UDT)