Timezones are not optional information

Posted: Mon, 11 November 2013 | permalink | 9 Comments

In high school, I had a science teacher who would mark your answers wrong if you forgot to include units. “What does that mean?”, he would write, “17.2 elephants?” The point he was trying to get across was that a bare number, without the relevant units, wasn’t precise enough to be useful. Also, carrying the units with you helped to cross-check your work – if you got a numeric answer, but the units were garbage (seconds per kilogram, for instance, when you were trying to find an acceleration), then you could be pretty sure you’d made a mistake somewhere.

Fast forward to today, and I’m currently working with a database containing a pile of timestamps. Without timezones. Where the timestamps being inserted are in local time. Thankfully, so far, this particular system has always been run in one timezone, so I’ve only got one timezone to deal with, but the potential ramifications of systems with different timezones inserting data into this database terrify me.

The naive answer is to just store everything in UTC and be done with it. I’m not particularly averse to that solution, as long as it’s very clear to everyone what’s going on. The correct answer, though, I think, is to always keep timezone information with your timestamps – otherwise, you’ll never know whether it’s 0830 in elephants…


9 Comments

From: Nicos
2013-11-11 17:44

All timezones with daylight savings are ambiguous for one hour every fall. Even if a system is used in a single timezone it’s still an issue. You need to store times in UTC if your system is running 24/7.

From: tomás zerolo
2013-11-11 20:10

Been through that too, feel your pain :-)

(The unsettling thing is that this was in an industrial context. Chemical industry. Shudder).

I’d like to even tighten your requirement a bit: timezone alone is a bit ill defined (Central European Time? What about summer/winter time)?

I’d require to add the explicit time zone offset (e.g. +02:00 or something). No symbolic time zones allowed.

From: Ross Reedstrom
2013-11-12 09:54

My take on this is that there’s two different kinds of timestamp data out there: timestamps that represent a particular point in the linear flow of time, often historical. Those are best stored as UTC. Worry about timezones only in accepting them to convert unambiguously to a UTC timepoint (and displaying them in the users local timzone, if they so desire). The other, much rarer kind of timestamp shows up in scheduling/calendar apps, and are used to denote times for human activity. Then, the location-in-space (timezone) is critical info, and needs to be stored alongside the stamp. Notice that there may in fact be more than one timezone of interest for a given timestamp - if more than one person is involved in a given activity. When scheduling a meeting w/ my colleague in München, it behoves me to know both my and her timezones.

From: Anonymous
2013-11-12 12:19

The other obnoxiously exciting problem: you’d think you could just always store UTC plus an offset, but it turns out that there are different time zones with the same UTC offset, but with different arcane rituals for gratuitous offset changes during the year. That matters when you’re trying to deal with time zone calculations.

From: tomás zerolo
2013-11-12 21:52

@Nicos: I did’nt see your comment last round.

Yes, once upon a time I held your position in a very strong way. Until I lost an argument (“but the customer sees that!”). That’s why I’ve softened my position since, accepting time zone offsets.

Said customer is still doing local time. Overall situation would be better if I’d had this idea then.

(And yes: I prefer UTC still. When I do logfiles I do use UTC, unless someone complains, then I try to convince them (the “sort” argument helps sometimes), then I switch to local+tzoffset).

It helps a lot to have a back-off position which still makes sense!

From: Russ Dill
2013-11-16 10:02

Yes, the only way to store times is UTC. If you have information passed to you in some other timezone, it’s pretty much guaranteed to be garbage at some point.

If you are thinking it is a good idea to store the timezone along with the time, and the timezone you are storing is not UTC, you are wrong. No argument with specifying that yes, the time stored is in UTC.

From: Jakub Narebski
2013-11-18 10:31

Or store everything in timezone-free time format, e.g. Unix epoch (which by definition is timezone-free), and optionally store numerical timezone to translate back to local time.

From: Baylink
2013-11-21 04:32

All timezones with daylight savings are ambiguous for one hour every fall.

No they’re not.

EST and EDT are two separate timezones; if you have the TZ info on the timestamp, there’s no ambiguity even if you’re storing localtime.

As I remind everyone, “this Sunday morning, 01:59:59EDT will be followed immediately by 01:00:00EST.”

If you’re working with things which let you enter localtimes without the timezone, you are, yes, screwed.

or as we said that Up North, "schrod".

But that’s a different issue.

The second commenter is right though: named TZs are also capable of ambiguity.

From: Kurt Roeckx
2013-11-27 02:09

Wanting to store it with a time zone and using boost::posix_time I ran into: https://svn.boost.org/trac/boost/ticket/9186

Post a comment

All comments are held for moderation; markdown formatting accepted.

This is a honeypot form. Do not use this form unless you want to get your IP address blacklisted. Use the second form below for comments.
Name: (required)
E-mail: (required, not published)
Website: (optional)
Name: (required)
E-mail: (required, not published)
Website: (optional)