
Time matters: when UTC time is just not enough - juliane-sander
https://lazystone.github.io//programming/time/2017/03/13/time-matters.html
======
joshstrange
No, No, No, NO.

Look I know TZ's and dates are hard, I work with them quite regularly, I have
written code to handle things like recurring items, and I have written a lot
of code around knowing what TZ to show a date in to an end user (in my case
you have to deal with things happening in a participant's TZ, their
supervisor's TZ, our call center's TZ, and whatever the TZ is of the browser
the end user is accessing the site from, all of which could be different). A
bad UTC time due to mismatched timezone databases is by far the least of my
worries, in fact it's not even on my radar nor do I plan to add it to it. You
HAVE to standardize, period. UTC makes the most sense (epoch timestamps if you
can get away with it) and it's up to the client to format that however they
want (moment.js is your friend if you are a web dev). Yes, keep your TZ
databases up to date and know which DB your software is using but use UTC.
Anything else is a recipe for disaster.

~~~
Joeri
I work on a room and equipment reservation system. To my users, the time of
the booking is always the time on the clock on the wall where the booking is.
It never changes, regardless of what insanity the time zones go through. So,
it is stored twice, as a wall clock timestamp without tz, which is
authorative, and as utc, derived every time the wall clock time is saved. It
is a best of both worlds solution, utc for when we need to compare against now
(to know when a booking becomes final) and wall clock time as authorative
source and for user display.

No disasters so far. ;)

~~~
joshstrange
This is an acceptable solution in your situation it wouldn't be in mine and
only storing local time is never acceptable IMHO.

~~~
andrewf
I disagree in cases like this, where local wall time is the source of truth. I
would go further than OP and not keep UTC time around in a separate column
either.

Why? On March 13 last year, Chile announced a new daylight savings change, to
take place on May 15. [1] Let's say I already had an appointment somewhere for
3:00pm on May 21. When the timezone change was announced, that reservation is
still at 3:00pm local. It is not still at the same time in UTC.

utc_time is derived from local_time and timezone, not the other way around.

(I am generally a fire-breathing advocate for UTC everywhere)

[1] [http://codeofmatt.com/2016/04/23/on-the-timing-of-time-
zone-...](http://codeofmatt.com/2016/04/23/on-the-timing-of-time-zone-
changes/)

~~~
shakna
Would not epoch time then make the most sense?

------
cyberferret
> Flight arrival and departure times

As a former commercial pilot, we use UTC _exclusively_ when flight planning
and reporting waypoints - no matter where we are in the world.

When I report that we expect to reach waypoint 'X' at 0451, then anybody
listening in ANYWHERE in the world knows exactly how long before I get there,
or how long overdue I am. No ambiguity whatsoever.

To this day, in my new life as a developer, I ensure that all my servers are
set to UTC and I always use UTC as my time reference datum.

~~~
mark-r
You're extremely lucky. Most of us have to deal with the fact that people are
more interested in local time than some idealized time.

~~~
cyberferret
Good point, and it is also a reason why we used 'humanised time' in our apps
where possible, i.e. the old "you posted this 7 minutes ago" rather than "you
posted this at 07:35" because it gives the same datum no matter where our
users are.

But even now when we schedule meetings with (IT) colleagues in other
countries, I will always reiterate/exchange the meeting times in UTC so that
there is no confusion at all when we start the sessions.

------
OtterCoder
Sorry, no. Clients can be responsible for themselves. All I care about is
providing unambiguous, standardized data to everyone. If they want to corrupt
it after that, it's on them, not me.

~~~
msandin
Unless you do recurrent scheduling. In that case it's important to understand
that you schedule in a particular timezone. Both because the relationship
between local time an UTC changes throughout the year with DST changes and
because evaluating an expression like "Thursdays at 03:00" might evaluate
differently if you first move the time to UTC (say 22:00) and then check if
it's a Thursday (no, it's only Wednesday still, but in 24 hours it'll be
time).

~~~
pavel_lishin
Doesn't this become completely unglued once you've got people in different
time zones, with different DST settings, using the same calendar?

If I have a meeting every day at 10am local time with someone in Arizona,
_their_ meeting suddenly jumps by an hour when _my_ time changes. (Similarly,
if it's my Arizonian colleagues who set the meeting to be at 10am daily, I'm
suddenly meeting them at 11am my time once spring hits.)

~~~
msandin
Indeed, but this is representative of a very real change in the relationship
between your clocks. The only way to keep this non-surprising is to make the
time zone the schedule runs in explicit to the user. If somebody in a
different zone edits the schedule it has to remain in the original time zone
unless they explicitly change it. You do all your scheduling calculations in
the schedule time zone and then convert it to an instant in time, likely
expressed as a UTC time, when you need to calculate how long your computer
should wait until the next event.

(Date)Times stored in the schedule (start times, end times, trigger times,
clock times) are not instants in UTC, they are time specification in a
calendar system which can only be interpreted as specific instants in the
context of a particular time zone. Which is stored explicitly and separately
from the calendar date-times.

This crucial separation of a time specifications in a calendar system from
instants in time is one thing which many date/time libraries get wrong and
which Noda-/JodaTime gets right.

------
codebeaker
Makes a classic "falsehoods programmers believe about time" mistake.

The example (which is not so uncommon) that an appointment is set in timezone
X, author of the service storing the appointment helpfully converts to UTC,
timezone of the originating country changes and then the "return" from UTC to
source timezone is incorrect, because now UTC-0600 is actually UTC-0700.

In short, any conversion that reduces precision is flawed. Dropping timezones
after normalizing them to a canonical format makes it impossible to restore
the conversion.

~~~
sp332
I'm not sure I buy your example. If you save the time in the local time, then
the time zone changes, the stored date will now be wrong. If you convert to
UTC, and update your time zone tables when the time zone changes, then when
you convert back the time will be correct.

~~~
teraflop
I think you're missing the point of the example. When I make plans to meet
someone "at 10AM Central time on Friday", I don't mean "let's meet at UTC
epoch 1489762800". I mean "let's meet when the clock says 10:00". If the
government suddenly decided to cancel daylight saving time on Thursday, then
our meeting would still happen at 10:00 on Friday, even though in absolute
(UTC) terms it would be an hour earlier.

A calendar appointment isn't identified by a UTC instant; it's identified by a
_predicate_.

~~~
fenwick67
Another example is recurring events. Let's say I have a meeting every Friday
at 4PM Central in Chicago. That's UTC 11:00 now, but it was UTC 10:00 last
week.

~~~
crazygringo
Which also makes it really interesting for people in _other_ time zones. E.g.
my recurring meetings, defined in eastern time zone, are the same for me this
week as last week (thank goodness) -- but they've moved by an hour for people
in other countries who are also in the meetings.

------
lucb1e
Summary:

Don't let the title throw you off, UTC is fine. Just be aware that when you
convert the UTC time that you store back to local time, the conversion might
be off depending on where you convert it. If your API is hosted on a server in
Amsterdam and your users in Japan, that won't work. If your app is run on a
tablet bought a few years ago, it will have an old timezone database. As the
article highlights:

> Do date/time transformation on the server side, where you can control it.

~~~
mark-r
It's more than that. If the daylight saving laws change in the zone you're
interested in, the round trip from local to UTC to local again can be off even
if your database is up to date and your timezones are correct. Converting from
local to UTC using the old rule then UTC to local with the new rule will
really mess you up.

~~~
lucb1e
The article offers no solution to that though.

> So, you had everything right, but result is wrong. Life is unfair. Deal with
> it.

It mentions a workaround, but not a solution.

~~~
mark-r
The solution is right there in the next paragraph. Realize when you need to
store local times instead of UTC.

There's no cookie cutter solution, you need to _think_ about your particular
problem and do what's best.

------
cbhl
IIRC, for the first few years, Facebook stored all event times in "local
time", but with the Pacific time zone.

So if your event was in 3 PM Eastern Time, it would be written to the database
as 3 PM Pacific Time.

Did it result in bugs? Yeah, sure. Should it have been fixed to use UTC? Of
course. But it wasn't the end of the world -- people lived with it for years
and it worked well enough. (Engineers grumbled about the code, of course.)

------
lostmsu
A simple concept for no-timezone time: instead of using a number for hours,
use some city (perhaps first one/two letters of the name) where it is noon at
that time. E.g. this post is around 15:00 PST in such system will be just
"samoa". 12:00 GMT will be "greenwich", 16:00 GMT - "moscow", e.t.c.

Does not solve the problem for local time (stores in one country will be open
from moscow to greenwich, in other - samoa to tokyo), but helps to put global
time in perspective. E.g. if its berlin, you can guess, that folks in Berlin
have around 5 hours more of work time, and folks in Moscow will be done in 4,
and Japan is enjoying nice evening already.

------
tscs37
>So, as an experienced programmer you always use UTC to store time. And you
are right!

Umm, no, as an experience programmer I don't touch UTC until I hit something a
human might read at this moment, otherwise I ship unix timestamps. I'm not
that insane.

The UI which is currently displayed has the best information available to use
the correct timezone, calendar, font, notation format and language to display
time.

Not hitting frontend but needs a human-readable timestamp for insane reasons?
UTC. All databases are UTC, all servers are UTC.

I'm not gonna touch time unless absolutely necessary and _if_ I'm going to do
it, I'm going to do it with a long pole and acid-resistant gloves on.

------
MarcusBrutus
In your database always store time as seconds since the Epoch (or milliseconds
since the Epoch if you need more precision). Forget about SQL date/time types,
they are useless. Then convert to "civilian format" only in the presentation
layer. You can't go wrong with that.

Seconds since the Epoch is unambiguous, eschews time zones, daylight savings
time and other man-made concepts (it makes sense to Martians too). Plus it
only requires an INTEGER type in the database which every RDBMS provides (so
is migration safe). You also create demand for your future colleagues (or
future self) around 2038. Or you can use BIGINT if that bothers you.

~~~
TheCoelacanth
Seconds since Epoch is always unambiguous, but you can't always unambiguously
convert a time to seconds since Epoch. If I set my alarm clock to 7 AM while I
am in EST and you save that as 11 AM UTC, then I travel to somewhere that is
in CST that 7 AM is now 12 PM UTC and the time you saved is now wrong.

------
cozzyd
A lot of online time calculators don't properly account for leap seconds. For
example, [http://billionbirthday.com/](http://billionbirthday.com/) doesn't
(of course, it can't account for yet-to-be-announced leap seconds, but it
could include currently known leap-seconds). Wolfram Alpha doesn't seem to
either (at least, it doesn't have the most recent leap second).

I only know this because I work on a balloon experiment and some of the
quantities our GPS reports are not in UTC Time (so don't have leap second
corrections).

------
keithnz
I deal with this, we monitor various sensor data, and for the most UTC is
never what's wanted, except for exporting and importing, but even then, I've
seen people want/give things in standard time, or even DLS time. Certain
things naturally need to be in local time like with water measuring over night
usage looking for leaks. So things are recorded with datetimeoffsets so you
can know when something occured locally to the sensor, as well as being able
to translate to UTC, or the local time as it is now, or the local time of the
viewer of the data

------
bluesmoon
I wrote this somewhat thorough post about time, date, events, and timezones in
response to a stackoverflow question once:
[http://stackoverflow.com/questions/2532729/daylight-
saving-t...](http://stackoverflow.com/questions/2532729/daylight-saving-time-
and-time-zone-best-practices/3269325#3269325)

It covers repeating events, historical accuracy, ranges, events that are fixed
in localtime but variable in UTC, and more.

TL;DR: Always store UTC datetime + TimeZone, sometimes you need to store a
little more information.

~~~
rolux
Sadly, there's a lot more to it:

[http://www.quirksmode.org/blog/archives/2009/04/making_time_...](http://www.quirksmode.org/blog/archives/2009/04/making_time_saf.html)

------
chaz6
Even UTC can be ambiguous when it comes to leap seconds. I don't know any
major DBMS that can correctly store a leap second event in a standard datetime
column. Perhaps the answer is to standardize on miliseconds since 1958
(19580101T000000Z) when TAI began.

------
cammio
we should all use: utc metric 24h rht

one can dream.

~~~
twic
No, Swatch Internet Time!

------
sujal99
Mountain out of a molehill!!!

~~~
macintux
When it comes to date/time processing, it's all molehills. The problem is that
there are many, many, many such molehills, and people keep claiming you can
ignore them, until you find yourself with a lame horse.

(lame-metaphors-r-us)

