Hacker News Reader

Comment by Arch485 | Hacker News Reader

This is actually incorrect, as per Postgres docs `timestamptz` is always stored as a UTC timestamp.

The benefit to this vs a regular timestamp is that when you insert/update a timestamp value, Postgres can then convert that timestamp to UTC if necessary before storing it, and if you select a timestamp value Postgres can convert it from UTC to the timezone you want.

If your connection is set to use UTC, and you always handle UTC timestamps, there probably isn't much practical difference between `timestamp` and `timestamptz`, however.

View on HN

> if you select a timestamp value Postgres can convert it from UTC to the timezone you want

I dread timestamp issues. Hard to understand what happens and how to fix them.

You mention "if necessary". How does postgres knows if a conversion is necessary?

If the value has a TZ offset it'll know. If it doesn't but the session TZ differs from UTC then it'll know. With non-TZ timestamp both cases could store the wrong timestamp and it might be impossible to catch or correct without the original data or more context.

Also if you're worried about the server having a different timestamp and possibly not having configured the connection timestamp correctly, as the following test shows Postgres always specifies the timezone of the timestamp it's giving you

server=# CREATE TABLE test1 (date TIMESTAMPTZ NOT NULL);

CREATE TABLE

server=# INSERT INTO test1 VALUES (NOW());

INSERT 0 1

server=# SELECT * FROM test1;

             date

------------------------------

2023-06-01 08:00:30.40968+02

(1 row)

server=# SET timezone = 'UTC';

server=# SELECT * FROM test1;

             date

------------------------------

2023-06-01 06:00:30.40968+00

(1 row)

> If your connection is set to use UTC, and you always handle UTC timestamps

Aren't timestamps always supposed to be utc?

I understood timestamps to be "seconds since 0:00 UTC January 1 1970", so absolute points in time that are not timezone-dependent. Wikipedia agrees with us too. Of course there is no official definition, and the term has evolved from an older concept (actual rubber stamps) so it's not surprising that others use it differently...

Internally they are with Pg. In practice non-TZ columns can go sideways. For example your app or ORM starts trying to store local time (Pg cannot know to save equivalent UTC), so now you've no way to correct and Pg cannot either. Or the DB config gets switched to non-UTC, again you may have no recourse.

I've seen both these things happen at companies. Often users don't care or notice for a long time, until suddenly they do and you're painted into a corner.

Reply on news.ycombinator.com