←back to thread

Timestamps and Time Zones in PostgreSQL (2016)

(phili.pe)
189 points GordonS | 1 comments | 30 Mar 19 21:47 UTC | HN request time: 0.001s | source
Show context
zeroimpl ◴[31 Mar 19 01:28 UTC] No.19533112[source]▶
I sometimes wish there was an actual "TIMESTAMP WITH TIME ZONE" type that stored the input timestamp as well. Basically, a version that didn't canonicalize the input by converting to UTC. This way, I can easily show timestamps to the user in the time zone they used to input them.
replies(6): >>19533287 #>>19534038 #>>19534057 #>>19534316 #>>19535066 #>>19541872 #
welder ◴[31 Mar 19 05:21 UTC] No.19534038[source]▶
Edit: I'm being downvoted, but I know more than you about timestamps. Check my bio before downvoting.

You want a column that can store both timestamp and timezone, so why not just have 2 columns? You already have to convert the timestamp into the user's timezone when rendering because the user's timezone can change. Storing timestamps with timezone just complicates things, for ex:

display_in_user_zone(TIMESTAMP UTC)

display_in_user_zone(TIMESTAMP_WITH_TIMEZONE)

The above is exactly the same, but the UTC one will prevent bugs in the long run.

The only time it's acceptable to store time with timezone is with DATES that do not have a TIME component.

If you really need to display the timestamp in the timezone the user originally entered, store the original timezone along with every timestamp.

replies(3): >>19534517 #>>19534530 #>>19535511 #
1. l0b0 ◴[31 Mar 19 08:35 UTC] No.19534517[source]▶
The main reason I want the original time zone is that converting to UTC means information loss. And compensating for that information loss is a PITA, because there is no separate time zone type, so now I have to store a bloody shambles of a UTC timestamp, a string corresponding to a time zone, possibly another string corresponding to the time zone database version (https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a...), and rely on every single language interfacing with the database treating timestamps and time zones in compatible ways, which is guaranteed not to be the case for the foreseeable future, and far from trivial to do even close to correctly in most languages. To focus on compatibility with the real world rather than performance databases could for example store all three pieces of information, plus possibly an internal representation purely for ordering and other performance-related purposes, and allow the user to fetch the results using any time zone (defaulting to the server time zone) and time zone database version (again defaulting to the server one). The application layer should not have to do these conversions.