18

Our app currently uses a local time rather than UTC. I know that it's important to use UTC, but cannot for the life of me remember why.

Assuming that the DateTimes are stored with an offset, when comparing a local time to a UTC time, or with another time with a different timezone, surely any library worth using will know about the different timezones and mutate the two objects into something that can be compared?

As long as the offset is passed around with the DateTime (which it will be using objects rather than strings, say), I do not see why it matters. Why should I deal with 2014-09-01T13:44:13+00:00 rather than 2014-09-01T14:44:13+01:00? In fact, storing as UTC looses the offset information (the local time when the time was declared).

What am I missing here?

Context: We are having limit, off-by-one style errors, and I thought 'aha: move all the things to UTC' but then realised that I am just going through the code converting a bunch of DateTime objects to use a UTC timezone, and this struck me as a waste of time.

Markus Malkusch
  • 7,738
  • 2
  • 38
  • 67
sennett
  • 8,014
  • 9
  • 46
  • 69
  • 1
    Go find the libraries that understand time zones...report back. Your premise "surely any library worth using will know about the different timezones" is not necessarily valid. Which language(s) are you considering? (A question untagged by the implementation language may not get as much attention as all that; there are 179 who follow 'datetime' and 10 who follow 'utc'.) – Jonathan Leffler Sep 03 '14 at 14:08
  • @JonathanLeffler While I agree with the attention, this question is perfectly independent on any language. – Markus Malkusch Sep 03 '14 at 14:53
  • Some details in [DateTime vs DateTimeOffset](http://stackoverflow.com/a/14268167/634824) may help in understanding - but they are mostly in favor of `DateTimeOffset`. The main cases against local datetimes arise when you *don't* have an offset. – Matt Johnson-Pint Sep 03 '14 at 14:59
  • If you don't use UTC consistently then sooner or later you simply lose control of the whole mess. – Hot Licks Sep 03 '14 at 16:43
  • [This link](http://yellerapp.com/posts/2015-01-12-the-worst-server-setup-you-can-make.html) has always helped me. – Wayne Werner Jan 08 '16 at 14:05
  • This answer explains that anything except UTC is broken in mysql https://stackoverflow.com/a/1650406/175071, which may also apply to other languages – Timo Huovinen Jul 28 '21 at 10:23

3 Answers3

13

There are two reasons that I store time as UTC.

First, is that with global users of some of the applications I work on, local time varies per user, so local time for a user that entered the data may not be local time for a user viewing the data later.

Second, timezones change. They are subject to the whims of governments. What is UTC +5 today could be UTC +6 tomorrow just because some government says so, which would then make the local time + offset different than what was stored. You can always figure out the correct local time, but I just view it as more work than just converting UTC to local.

Those are the best reasons that I am aware of for using UTC, but I am sure there are others I haven't thought of.

Nick Zimmerman
  • 1,471
  • 11
  • 11
  • 1
    `2014-09-01T14:44:13+0100` will always represent the same point in time, regardless what governments say. – Markus Malkusch Sep 03 '14 at 14:42
  • @MarkusMalkusch It is the same point in time, but you have to do a little more work to display it to local time. I will edit my post to relect that. Thanks for pointing it out. – Nick Zimmerman Sep 03 '14 at 14:50
  • @MarkusMalkusch - Once it has *passed* yes. But to Nick's point, one can't assume that the offset for that timestamp will be the same offset for another, or that for future timestamps that the government won't change their mind between "now" and the timestamp. But that's more of an argument for using a full time zone instead of an offset - not for using UTC. – Matt Johnson-Pint Sep 03 '14 at 14:53
  • 1
    I vote for the UTC approach for 2 additional reasons: because it's a standard that reflects into architecture: server side (UTC) - client side (local timezone) & because programming languages are generally built so that they can construct date and time objects from unix timestamps which are (i think exclusively) obtainable in UTC. Very consistent and likeable cooperation between DB, server and client side which can all work with a number instead of a complex Date object (e.g. Long in Java) – Sandman Sep 21 '14 at 12:55
  • 1
    I would also flip the question around: why should you care about the offset? What's the additional information that offset brings? Offsets are not standard (client-side by definition) while UTC is. – Sandman Sep 21 '14 at 13:12
10

Your use case allows storing a local time with an offset. IMO there is no need to store in UTC, as you can always translate the point in time into any other local time. In fact it gives you even more, as you have additionally the information about the local time.

Technically there is an advantage in storing the date as UTC and the offset separately: You can sort on a string, which gives you an advantage in e.g. DBS which don't support date fields with offsets properly (e.g. MySQL).

If you consider systems which don't allow storing an offset, the actors will have to agree on a common timezone. UTC seems to be a very stable candidate with good timezone translation support.

One example for this is Exif where you can only store a local date without any timezone information. Another example is the hardware clock in a multi OS computer. If the actors don't agree on a common timezone, you will get headaches (Linux & Windows can get funny during DST switch).

Markus Malkusch
  • 7,738
  • 2
  • 38
  • 67
  • The point about indexing is spot on. But keep in mind that some databases support storing the date, time, and offset in a single value (usually called `datetimeoffset` or `timestamp with time zone`) - and in those cases the db platform itself will convert to UTC as it builds the index. – Matt Johnson-Pint Sep 03 '14 at 14:55
  • The Exif example is much more direct. There are plenty of other cases like this where you must conform to a single date+time field and can't store an offset. +1 – Matt Johnson-Pint Sep 03 '14 at 14:57
  • Thanks for your reply. My question was because we are having limit, off-by-one style errors, and I thought 'aha: move all the things to UTC' but then realised that I am just going through the code converting a bunch of DateTime objects to use a UTC timezone, and this struck me as a waste of time. Do you have any opinions about how the data should be handled within the code (rather than the storage side of things)? – sennett Sep 03 '14 at 16:35
  • This answer was likely the way to go back in 2014. In 2022 with cloud computing etc. it would take a unique use case to not store time in UTC. And push all the time zone logic to the edge of the application - typical the SPA. Here we know the time zone, and it becomes trivial to convert from UTC. – xcskilab Mar 04 '22 at 18:45
2

I think the best is to store DateTIme+Offset

The point is you must always store DateTime either in UTC or with in offset, but never store DateTime "local centric", or without the offset. In other words:

Best! DateTime+Offset

2014-09-03 11:36:07 EDT -04:00

Good UTC:

2014-09-03 15:36:07 UTC

Veryyyyy bad: DateTime no offset

2014-09-03 11:36:07

For instance, this could happens if you don't really care of time zone and at the end the previous time could happen to be stored as:

2014-09-03 11:36:07 UTC

Now, depending on your language framework, it might happens that you might need to convert your DateTime/Offset in something different, but my advice is to store always as the first form....

tomsoft
  • 4,448
  • 5
  • 28
  • 35