[c0l0]

The problem highlighted here causes one of the few gripes I have with PostgreSQL, and its logging capabilities in particular: It seems to only support logging time in one single format, which does contain a timezone identifier - but using the potentially ambiguous shorthand name.

A few moons ago, that led me down a whole new rabbit hole of its own that is Golang's time zone parsing capabilities (https://github.com/golang/go/issues/9617), which, at least for Elastic's filebeat, seem to vary at runtime depending on whether or not you have a time zone database available...

Moral of the story: Please just conform to ISO-8601 everywhere.

[de_Selby]

I'd say the moral is that server side should always be in UTC.

[NateEag]

In some cases, storing times in UTC opens you up to possible errors:

https://codeofmatt.com/on-the-timing-of-time-zone-changes/

I think the right thing is to store times with the timezone the user wanted when they created the time. You can often use defaults or other UX niceties to streamline specifying TZ, but not always.

Also, if you'll want to unambiguously know the timestamp's exact point in time at some point (like for comparison with other dayetimes), you should save the offset from UTC alongside the actual datetime. Otherwise, around clock changes like "fall back" you cannot tell if you're seeing 2 AM for the first or second time.

This is the best I've come to trying to piece datetime storage together. If someone has better advice, I'd love to hear it.

For more of my theory on timezone handling and the sources I've used to help me think it through, see http://howicode.nateeag.com/dates-and-times.html .

[HideousKojima]

.NET's DatetimeOffset class does this really well, and what's nice is you can access the local system's timezone database to convert it to whatever the local time was in that timezone for a given date (it's aware of when DST or timezone changes occured). I'm sure several other languages have similar tools too.

It doesn't solve every conceivable timezone issue, but it solves 99% of the problems most developers would have with it.

[FabHK]

Unless sub-second accuracy is required, I'd be in favour of UT1. It doesn't use SI seconds (while TAI and UTC do), but it has the advantage that a day has exactly 86400 seconds, so you don't ever have to deal with "23:59:60" timestamps.

Summary:

UT1 - noon is when the sun is above you, day has 86400 seconds (not SI seconds)

TAI - noon drifts away from when the sun is above you, day has 86400 SI seconds

UTC - noon is when the sun is above you (+/- 1 second), day has 86400+/-1 SI seconds

Difference UT1-TAI: continuously diverging

Difference UT1-UTC: continuously diverging up to less than +/-1 second, then discretely jumps back when it gets too big (leap seconds)

Difference TAI-UTC: increasing in discrete increments of +/-1 full second (leap seconds)

[Unklejoe]

I'd rather just use TAI and never have to worry about anything. Let the conversion to UTC be the client's problem.

Having the length of a second always be the same is kind of important.

[FabHK]

For logs, fine. But you do deviate more and more from what the world is using (as of 2017, TAI is exactly 37 seconds ahead of UTC, according to Wikipedia)

[outadoc]

Not true. Say you want to save an event that you know will happen on the 14th of July at 15:00 Paris time, in the year 2025. Current time zone rules tells you this will be at 13:00 UTC. Well, if France decides to abolish DST and stick to standard time before then, you'll be wrong.

[eqvinox]

No, they won't be wrong, because the TZ database includes historical data & will know that DST was still in effect at the time you saved the timestamp.

[Ed. Nevermind, sorry, I see you're referring to a future point. My bad. You're right.]

[de_Selby]

For that use case definitely, though a law change is a bit of an edge case.

For stamping times like in a log though UTC should always be used server side.

[outadoc]

Sure! But my point is there's no one true way to do it and you always have to think about what you're trying to represent and how you're going to use it.

Also, I would argue time manipulation is full of many "edge cases" like this one, which is why it's so hard. It's going to work fine until it doesn't.

[jnye131]

OR.. should they be in TAI? https://en.wikipedia.org/wiki/International_Atomic_Time

[zaarn]

Surprisingly, a bunch of software doesn't like it when you setup TAI, if you manage it to begin with.