Re: Best way to store the NodaTime datatypes in a (SQL server) database

[Jon Skeet]

On 15 June 2012 13:21, RWCH <r.chris...@telfort.nl> wrote:

What is the best way to store a NodaTime.Instant or NodaTime.ZonedDateTime in a (SQL server) database?

It partly depends what you want to do with it. For an Instant, the simplest way would be to store a long (64 bit) representing Instant.Ticks. On the other hand you certainly could use DateTimeOffset, which would work better with other code using the database, potentially.

For ZonedDateTime I would probably store the Instant (however you want to store that) and the time zone ID. As you say later on, just storing the Instant and Offset (or local time and offset) doesn't preserve all the behaviour - two different time zones could still have the same offset at a particular instant.

Another option for ZonedDateTime would be to store the local time (in whatever form you want, possibly DateTime) and then time zone ID and offset.

I'm assuming that if you've got a ZonedDateTime you actually care about the time zone - in which case you presumably want to preserve it :)

Jon

[Carsten Voss]

Hi,

My guess would be that in most cases it would be enough if an application knows its reference Time zone. With that you can store the values as regular Dates which would then belong to the time zone of the server. Client operations would be done in the specific local time zone against the reference.

Carsten

[chaq...@googlemail.com]

On 16 Jun., 12:12, Carsten Voss <carsten.v...@gmx.de> wrote:
> Hi,
>
> My guess would be that in most cases it would be enough if an application knows its reference Time zone. With that you can store the values as regular Dates which would then belong to the time zone of the server. Client operations would be done in the specific local time zone against the reference.

I work with a CRM system which operates exactly like that: it stores
dates as regular dates without any time zone information, but uses a
"base time zone" as reference and clients translate those "implicitly
zoned" dates to dates in their client time zone.


Stephan.