Why doesn't the timezone changes in these SQL expressions?

Question

I'm trying to update the timezone in a certain field of one of my tables. I have tried in several ways seen in some SO posts, but nothing seems to change the actual timezone value.

Anyone can explain why ...

select 
    now(), 
    extract(timezone from now()) * interval '1sec', 
    (now()::timestamp || 'PST8PDT')::timestamptz, 
    extract(timezone from (now()::timestamp || 'PST8PDT')::timestamptz) * interval '1sec'

All of these fields have the same timezone? The results to me in Fortaleza/Brazil are:

2018-10-29 14:52:04.55495-03 | -03:00:00 | 2018-10-29 18:52:04.55495-03 | -03:00:00"

Why? Shouldn't the last 2 values have PST timezone?

How can I actually change the timezone for a timestamp with time zone field?

https://stackoverflow.com/questions/5876218/difference-between-timestamps-with-without-time-zone-in-postgresql — cske, Oct 29 '18 at 19:35
Don't use `PST8PDT`. Use a locality based identifier, such as `America/Los_Angeles` for US Pacific time. For your time zone, use `America/Fotraleza`. — Matt Johnson-Pint, Oct 30 '18 at 14:45

Erwin Brandstetter · Accepted Answer · 2018-10-30T01:03:38.753

You cannot change the time zone because the data type timestamp with time zone does not actually store the time zone to begin with. The time zone only serves as input / output modifier. Internally, the respective UTC timestamp value is stored.

The timezone setting of the current session determines the display of the value. To change the display, change the timezone setting. (I.e. pretend to be in a different time zone, where the current time is displayed differently.)

Example:

SET timezone = 'PST8PDT';

Then retry your test.

The AT TIME ZONE construct might also be instrumental.

Why doesn't the timezone changes in these SQL expressions?

1 Answers1