For timestamp with timezone, the internally stored value is always in UTC

Question

According to the documentation, "for timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.".

This explanation appears to conflict with the statement regarding the handling of timestamp with time zone. For instance, I set the system's TimeZone to US/Eastern and executed the command select '2025-02-12T6:30:04'::timestamptz. I anticipated the result to be 2025-02-12T11:30:04+00:00, as there is no time zone specified in the input string 2025-02-12T6:30:04. Therefore, it should default to the system's TimeZone parameter, which is US/Eastern, and subsequently convert to UTC using the offset between UTC and US/Eastern.

However, the actual return result was 2025-02-12T06:30:04-05:00, which represents a timestamp in US/Eastern rather than UTC.

Am I misunderstanding the documentation, or is there an error in the documentation itself?

Are you sure the problem isn't just the way the result is being displayed? — Mark Ransom
– Mark Ransom, Commented Feb 12 at 23:14
This question is similar to: Ignoring time zones for Postgres timestamps. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — JohnH
– JohnH, Commented Feb 12 at 23:38
1) Did you really SET TimeZone = 'EST'? Not a good idea as that pins to one of two time zones that exist on the east coast. Better to use something like set timezone = 'US/Eastern';. 2) The result is correct, this '2025-02-12T6:30:04' is taken to be at EST and rotated to UTC for storage. On retrieval it is rotated back from UTC to EST. To get what you want: select '2025-02-12T6:30:04'::timestamptz at time zone 'UTC'; 2025-02-12 11:30:04. 4) For the complete why?, see Time Stamps. — Adrian Klaver
– Adrian Klaver, Commented Feb 12 at 23:53
@MarkRansom That's what I am trying to understand. Are both '2025-02-12T11:30:04+00:00' and '2025-02-12T06:30:04-05:00` in UTC but they just display differently? — YEL
– YEL, Commented Feb 13 at 3:12
Again read this link Time Stamps section it explains what you are seeing. — Adrian Klaver
– Adrian Klaver, Commented Feb 13 at 17:20

Erwin Brandstetter · Accepted Answer · 2025-02-13 07:12:26Z

2

Both results represent exactly the same timestamptz value, the same point in universal time (Einstein aside). Only the display differs. See what happens when I run this locally, with timezone = Europe/Vienna:

test=> SELECT timestamptz '2025-02-12T11:30:04+00:00' AS t1
test->      , timestamptz '2025-02-12T06:30:04-05:00' AS t2
test->      , timestamptz '2025-02-12T06:30:04-05:00' = timestamptz '2025-02-12T11:30:04+00:00' AS the_same;

           t1           |           t2           | the_same 
------------------------+------------------------+----------
 2025-02-12 12:30:04+01 | 2025-02-12 12:30:04+01 | t

I get a third, equivalent display for the same point in time. Postgres displays timestamptz values according to the timezone setting of the session. If you prefer timestamptz values to be displayed for the UTC timezone, you might tell Postgres that you currently operate in that time zone by setting your session variable: SET timezone = 'UTC';. Of course, Postgres will then assume UTC where no time zone is given explicitly.

Also, time zone abbreviations like 'EST' are a crude tool that only encode a constant time offset. To be smart about daylight saving time (and other political changes across history), use a time zone name, like 'US/Eastern' - like Adrian already suggested in the comments.

See the manual on time zones and:

Ignoring time zones for Postgres timestamps

Don't fall for the idea that the data type timestamp with time zone would somehow carry a time zone. It does not. See:

Time zone storage in data type "timestamp with time zone"

edited Feb 13 at 7:12

answered Feb 13 at 1:45

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

YEL Feb 13 at 3:01

Hi thanks for the details! I understand that both '2025-02-12T11:30:04+00:00' and '2025-02-12T06:30:04-05:00` are the same. I was trying to understand what the documentation says 'for timestamp with time zone, the internally stored value is always in UTC'. But if I set the timezone to be 'US/Eastern', the stored value is not in UTC. Or you are saying that both '2025-02-12T11:30:04+00:00' and '2025-02-12T06:30:04-05:00` are in UTC but they just display differently?

Erwin Brandstetter Feb 13 at 3:39

@YEL Strictly speaking, the displayed timestamptz literals are not "in UTC". Internally, values are stored in UTC, which is binary identical to timestamp values showing the same clock time. This actually matters. See: dba.stackexchange.com/a/134603/3684

YEL Feb 13 at 22:39

I think I just need to understand what does 'internally' mean ><

YEL Feb 18 at 14:12

Ah I understand it now thanks!

Collectives™ on Stack Overflow

For timestamp with timezone, the internally stored value is always in UTC

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related