3 ms·

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

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.

PaoloBarbolini 3y ago
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)
foverzar 3y ago
> If your connection is set to use UTC, and you always handle UTC timestamps Aren't timestamps always supposed to be utc?
- remram 3y ago
  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...
- paulryanrogers 3y ago
  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.
m_0x 3y ago
> 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?
- paulryanrogers 3y ago
  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.

PaoloBarbolini 3y ago

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)

foverzar 3y ago

> If your connection is set to use UTC, and you always handle UTC timestamps Aren't timestamps always supposed to be utc?

remram 3y ago
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...
paulryanrogers 3y ago
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.

m_0x 3y ago

> 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?

paulryanrogers 3y ago
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.