107

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?

JSON
  • 1,583
  • 4
  • 31
  • 63
Frederico
  • 1,071
  • 2
  • 8
  • 3
  • Related question: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices – Oded Jan 17 '11 at 16:51
  • 1
    See also: [DateTime vs DateTimeOffset](http://stackoverflow.com/a/14268167/634824) - written for .Net, but conceptually applies to SQL also. – Matt Johnson-Pint Jul 10 '14 at 00:04

3 Answers3

147

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)

Marcel Toth
  • 10,716
  • 4
  • 22
  • 17
  • 3
    This is the best explanation I have read about when it would be appropriate, and I have read a lot For us, from this, it does not seem so. We get the time in UTC for our external data, and we know the location from another source if needed (and it never has been). Thanks for making it seem so obvious. – Andrew Sep 25 '12 at 15:49
  • 22
    you said "datetimeoffset stores UTC time and ALSO offset to the local time of the client", but datetimeoffset stores LOCAL time + Offset, or UTC time + offset equal +0. – Serhii Kyslyi Nov 07 '13 at 12:59
  • although you could just cast the DTO to a DT since the DT base is UTC (although this would be an extra step for everyone using the database, and is arguably no simpler than just using UTC time alone) – John Smith Jan 23 '16 at 04:45
  • 2
    Seems more like DateTmeOffset stores "Local Time and UTC Offset", not "UTC Time and UTC Offset". If you cast to datetime or use any of the datepart functions, you get the local date and time components. – Triynko Dec 14 '16 at 17:00
  • "*all clients know UTC time of all data and also local time in the place where information originated"* Though storing as part of the date field feels unnormalized in that case. What's the use case -- that is, why wouldn't you use UTC & pull out offset to `InputLocationId` (or similar normalized entity). There'd be calculation involved (hello, [exceptions](https://www.worldtimezone.com/faq.html)... especially [you, Indiana](https://en.wikipedia.org/wiki/Time_in_Indiana)), but it's still a deterministic process - and then the balance of the app's datetime logic is straightforward. – ruffin Jul 31 '18 at 17:33
  • Thanks @SerhiiKyslyi and Triynko, it is, of course, Local time + Offset, not UTC + Offset – Marcel Toth Nov 21 '19 at 23:30
26

You are absolutely correct to use UTC for all historical times (i.e. recording events happened). It is always possible to go from UTC to local time but not always the other way about.

When to use local time? Answer this question:

If the government suddenly decide to change daylight savings, would you like this data to change with it?

Only store local time if the answer is "yes". Obviously that will only be for future dates, and usually only for dates that affect people in some way.

Why store a time zone/offset?

Firstly, if you want to record what the offset was for the user who carried out the action, you would probably be best just doing that, i.e. at login record the location and timezone for that user.

Secondly if you want to convert for display, you need to have a table of all local time offset transitions for that timezone, simply knowing the current offset is not enough, because if you are showing a date/time from six months ago the offset will be different.

Ben
  • 34,935
  • 6
  • 74
  • 113
  • 4
    Windows UTC to local time conversion take into account when the daylight savings time changes for historical dates. I don't see why you'd want to store local time in that case. – jamiegs Mar 16 '11 at 13:06
  • 1
    @Jamiegs Windows only historically knows the "last historical information" (as is hinted at/noted in the .NET DateTime documentation). It is not comprehensive. –  Nov 27 '12 at 05:53
  • 2
    of course, you also have to store the location of the event, otherwise you can not say at what "local time" the event happened. – keuleJ Sep 06 '17 at 11:36
21

A DATETIMEOFFSET gives you the ability to store local time and UTC time in one field.

This allows for very simple and efficient reporting in local or UTC time without the need to process the data for display in any way.

These are the two most common requirements - local time for local reports and UTC time for group reports.

The local time is stored in the DATETIME portion of the DATETIMEOFFSET and the OFFSET from UTC is stored in the OFFSET portion, thus conversion is simple and, since it requires no knowledge of the timezone the data came from, can all be done at database level.

If you don't require times down to milliseconds, e.g. just to minutes or seconds, you can use DATETIMEOFFSET(0). The DATETIMEOFFSET field will then only require 8 bytes of storage - the same as a DATETIME.

Using a DATETIMEOFFSET rather than a UTC DATETIME therefore gives more flexibility, efficiency and simplicity for reporting.

PapillonUK
  • 642
  • 8
  • 20
  • Too bad entity framework has no way whatsoever to access the local datetime of a datetimeoffset field, making it impossible to query for a particular local date. – Triynko Dec 14 '16 at 17:01
  • @Triynko can you explain what you mean? Example? – reidLinden Dec 15 '16 at 16:38