Postgres sql time zone

Question

I try return values for specific time zone and I faced with some strange behavior in response:

SELECT created_at AT TIME ZONE 'US/Pacific' - created_at,
       NOW() AT TIME ZONE 'US/Pacific' - NOW(),
       NOW() now  FROM "my_table" 
ORDER by id DESC LIMIT 1

return

10:00:00 | -10:00:00 | 2017-08-02 17:36:30.660477+03

why does same function return opposite values (-10/+10) for the column from the table and for dynamic time?

Thanks!

Don't put "Solved" into the title. If Usagi's answer solved your problem, accept the answer. Otherwise this question remains "open" — user330315
– user330315, Commented Aug 2, 2017 at 18:06

Usagi Miyamoto · Accepted Answer · 2017-08-02 15:15:17Z

3

Type timestamp without timezone is interpreted as "local time zone".
Thus your created_at AT TIME ZONE 'US/Pacific' value is taken as a timestamp of the given timezone.

While NOW() returns timestamp with timezone, thus your NOW() AT TIME ZONE 'US/Pacific' value has to be converted to a different timezone.

See docs:

EDIT

Try this:

SELECT created_at AT TIME ZONE 'US/Pacific' AS crated_at_us_pacific, created_at,
       NOW() AT TIME ZONE 'US/Pacific' AS now_us_pacific, NOW() AS now
   FROM "my_table"
   ORDER by id DESC
   LIMIT 1

answered Aug 2, 2017 at 15:15

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

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

2 Comments

Oleh Sobchuk Over a year ago

Please. change your answer from SELECT created_at AT TIME ZONE 'US/Pacific' AS .... to SELECT created_at AT TIME ZONE 'localtime' AT TIME ZONE 'US/Pacific' AS ..... After that, I mark it as useful

Usagi Miyamoto Over a year ago

The purpose of the SELECT statement was to show the reason why the values differed, not to give an advice how to resolve, as your question was "why"...

Oleh Sobchuk · Accepted Answer · 2017-08-10 09:35:47Z

0

Thanks Usagi Miyamoto for some clarification it sent me to right track.

If field timestamp without timezone AT TIME ZONE define specific timezone. So first declare localtime and then turn it to specific timezone: So fixed SQL is:

SELECT created_at AT TIME ZONE 'localtime' AT TIME ZONE 'US/Pacific' - created_at,
       NOW() AT TIME ZONE 'US/Pacific' - NOW(),
       NOW() now  FROM "my_table" 
ORDER by id DESC LIMIT 1

EDIT

More dynamically

SELECT created_at::timestamptz AT TIME ZONE 'US/Pacific' - created_at,
       NOW() AT TIME ZONE 'US/Pacific' - NOW(),
       NOW() now  FROM "my_table" 
ORDER by id DESC LIMIT 1

first timestamptz converts time to local timezone and after that to timezone which you need AT TIME ZONE 'US/Pacific'

edited Aug 10, 2017 at 9:35

answered Aug 3, 2017 at 8:21

Oleh Sobchuk

3,7222 gold badges31 silver badges45 bronze badges

Collectives™ on Stack Overflow

Postgres sql time zone

2 Answers 2

EDIT

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related