←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.225s | 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. OliverJones ◴[31 Mar 19 13:38 UTC] No.19535511[source]▶
Righto. Solicit a time-zone preference from each user, using the zoneinfo naming system ('Asia/Kolkata' or 'America/Indiana/Vincennes') Then accept times from, and render times to, that user in their local time. DBMS timezone settings do a good job of that. When users decline to give a preference, show them UTC until they get tired of it and give you a preference.

Finally, boycott Oracle's table server. It's horrible in this respect.