sql - Storing DateTime (UTC) vs. storing DateTimeOffset

Question

I usually have an "interceptor" that right before reading/writing from/to the database does DateTime conversion (from UTC to local time, and from local time to UTC), so I can use DateTime.Now (derivations and comparisions) throughout the system without worrying about time zones.

Regarding serialization and moving data between computers, there is no need to bother, as the datetime is always UTC.

Should I continue storing my dates (SQL 2008 - datetime) in UTC format or should I instead store it using DateTimeOffset (SQL 2008 - datetimeoffset)?

UTC Dates in the database (datetime type) have been working and known for so long, why change it? What are the advantages?

I have already looked into articles like this one, but I'm not 100% convinced though. Any thoughts?

Answer 1

There is one huge difference, where you cannot use UTC alone.

• If you have a scenario like this

  • One server and several clients (all geographically in different timezones)
  • Clients create some data with datetime information
  • Clients store it all on central server

• Then:

  • datetimeoffset stores Local time of the client and ALSO offset to the UTC time
  • all clients know UTC time of all data and also a local time in the place where the information originated

• But:

  • UTC datetime stores just UTC datetime, so you do not have information about local time in the client location where data originated
  • Other clients do not know the local time of the place, where datetime information came from
  • Other clients can only calculate their local time from the database (using UTC time) not the local time of the client, where the data originated

Simple example is flight ticket reservation system ... Flight ticket should contain 2 times: - "take off" time (in timezone of "From" city) - "landing" time (in timezone of "Destination" city)