Deconstructing ZonedDateTime

user_14226270 0
Viewed 222

Using NodaTime, and I'm looking to deconstruct a ZonedDateTime in order to save it to a SQL database. It seems to me there are a few options. I could deconstruct it to Instant and DateTimeZone and save it as a datetime2 and nvarchar(50). I could deconstruct it to DateTimeOffset and DateTimeZone or LocalDateTime and DateTimeZone and, in either case, save it as datetimeoffset and nvarchar(5).

Is there a difference, or reason to chose one over the other?

The only think I can think of is that the datetimeoffset plus nvarchar(50) might be better in case the db is ever read by a service that doesn't have as robust a timezone -> offset conversion system as NodaTime. In that situation I've at least capture what the offset was, in that timezone, at that point in time, which is lost (or at needs to be recalculated from historical timezone information) with a datetime2 plus nvarchar(50) approach.

Are there other considerations I'm missing?

nodatime
1 Answers

I would suggest using a datetimeoffset and a separate time zone ID. I'm assuming datetimeoffset still allows you to perform a total ordering (i.e. by instant) - although I suppose it's possible that that is less efficient than if you've stored a datetime2. It also may well take more space in the database, given that it's storing more data.

Even if the database is read by a service that does have time zone conversion operations, storing the offset in the database allows you to perform queries over the data based on the local date, e.g. "Show me all my appointments on Tuesday". You can't perform that query purely database-side if you only have instants.

One other thing you might want to consider if you're storing future date/time values is that the predicated time zone offset may change due to changes in rules. If your original input data was as a local date/time (which is usually the case if you're working with ZonedDateTime) then the datetimeoffset approach is storing "what the user gave you" plus the inferred offset - you can easily then update all the data with a later version of the time zone database if necessary. If you only have the computed instant, you'd need to work out what the original local date/time was in the "old" time zone database before adjusting it to the "new" time zone database. That may also have lost information, e.g. if the input value used to be ambiguous (so you picked one offset or the other) but no longer is.

user_226560
Related