Problem
I normally have a “interceptor” that handles DateTime conversion (from UTC to local time, and from local time to UTC) shortly before reading/writing from/to the database, so I can use DateTime. Now you can do (derivations and comparisons) all throughout the system without having to bother about time zones.
There’s no need to worry about serialization or transporting data between machines because the datetime is always UTC.
Should I keep saving my dates in UTC format (SQL 2008 – datetime) or should I use DateTimeOffset (SQL 2008 – datetimeoffset) instead?
UTC Why modify dates in the database (datetime type) that have been operating and known for so long? What are the benefits?
I’ve previously done some research on articles like this one, but I’m still not convinced. What are your thoughts on this?
Asked by Frederico
Solution #1
There is one significant difference: UTC cannot be used alone.
A simple example is a reservation system for airline tickets… There should be two times on the flight ticket: – “departure” time (in timezone of “From” city) – the “landing” period (in timezone of “Destination” city)
Answered by Marcel Toth
Solution #2
The usage of UTC for all historical eras is perfectly proper (i.e. recording events happened). Going from UTC to local time is always possible, but not always the other way around.
When is it OK to use local time? Please respond to the following question:
If the response is “yes,” just local time will be saved. Obviously, this will only apply to future dates, and usually only to dates that have an impact on people.
Why would you want to save a time zone/offset?
To begin, if you want to record the offset for the user who performed the action, you should probably just do that, i.e. record the user’s location and timezone at login.
Second, if you want to convert for display, you’ll need a table of all local time offset transitions for that timezone; merely knowing the current offset isn’t enough because the offset will be different if you’re displaying a date/time from six months ago.
Answered by Ben
Solution #3
A DATETIMEOFFSET allows you to store both local and UTC time in a single field.
This enables very simple and efficient reporting in local or UTC time without the need for any data processing prior to presentation.
Local time for local reporting and UTC time for group reports are the two most prevalent needs.
The local time is recorded in the DATETIME portion of the DATETIMEOFFSET, and the OFFSET from UTC is stored in the OFFSET section, so conversion is easy and may be done at the database level because it does not require knowledge of the timezone the data comes from.
You can use DATETIMEOFFSET if you don’t need times down to milliseconds, such as minutes or seconds (0). The DATETIMEOFFSET field will only take up 8 bytes of storage, just like a DATETIME.
As a result, C DATETIME provides greater reporting flexibility, efficiency, and simplicity.
Answered by PapillonUK
Post is based on https://stackoverflow.com/questions/4715620/storing-datetime-utc-vs-storing-datetimeoffset