Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing DateTime (UTC) vs. storing DateTimeOffset

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?

like image 769
Frederico Avatar asked Jan 17 '11 16:01

Frederico


People also ask

Is DateTimeOffset better than DateTime?

DateTime supports only two possibilities at this point: the local time of the application or UTC. Since the problems of DateTime were recognized early, there was a much better alternative in the form of DateTimeOffset, which has been the recommended variant since .

Should you store dates in UTC?

It is important that there is consistency across all your date-related data. When storing dates in the database, they should always be in UTC. If you are not familiar with what UTC is, it is a primary time standard that all the major timezones are based on. The major timezones are just offsets from UTC.

Should you always use DateTimeOffset?

If you need to know when things actually occurred, with more precision than just the approximate date, and you can't be 100% sure that your dates are ALWAYS stored in UTC, then you should consider using DateTimeOffset to represent your datetime values.

What is the difference between DateTimeOffset and DateTime?

With its Kind property, DateTime is able to reflect only Coordinated Universal Time (UTC) and the system's local time zone. DateTimeOffset reflects a time's offset from UTC, but it doesn't reflect the actual time zone to which that offset belongs.


2 Answers

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)

like image 88
Marcel Toth Avatar answered Sep 24 '22 05:09

Marcel Toth


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.

like image 44
Ben Avatar answered Sep 21 '22 05:09

Ben