CODEWITHSUNDEEP
postgresqltimezonetimestamp-with-timezone
Nelson Teixeira
Nelson Teixeira

Reputation: 6562

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

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?

Upvotes: 1

Views: 60

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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.

Related:

Upvotes: 1

Related Questions