←back to thread

Timestamps and Time Zones in PostgreSQL (2016)

(phili.pe)
189 points GordonS | 2 comments | 30 Mar 19 21:47 UTC | HN request time: 0.451s | source
Show context
mjevans ◴[31 Mar 19 00:52 UTC] No.19532963[source]▶
I think I slightly disagree about the choice of using timestamptz over timestamp. The main reason being that when I feed data in to the database I always want that same data back out. From the description I now realize that if I backed up and restored, or otherwise ran the same database with a different configuration the database might "try to be smart" and return a modified value.

I guess to store a timestamp and a timezone I should use a timestamp a second column with the timezone value (maybe a string, but I'd like a clean way of telling the database I want an 'enum' but to dynamically just add new values as it sees them; yes for PostgreSQL I'd do that with a secondary lookup table and an insert/update trigger).

replies(2): >>19533076 #>>19535839 #
1. minitech ◴[31 Mar 19 01:16 UTC] No.19533076[source]▶
It would return an equivalent value, which is the important thing. You can specify `AT TIME ZONE 'UTC'` (or whichever timezone) when consuming it to get a consistent look. On the other hand, it’s not clear from just the value what point in time a `timestamp without time zone` represents. That’s why I agree that it should be used when you don’t want to represent a point in time.
replies(1): >>19533511 #
2. colanderman ◴[31 Mar 19 03:01 UTC] No.19533511[source]▶
Moreover, `AT TIME ZONE` only works correctly when used following the semantics described in the article. It will do the opposite of what you think it should if you use `timestamp without time zone` to represent an absolute time, or vice-versa.