CODEWITHSUNDEEP
postgresql
Oleh Sobchuk
Oleh Sobchuk

Reputation: 3722

Postgres sql time zone

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!

Upvotes: 1

Views: 66

Answers (2)

Oleh Sobchuk
Oleh Sobchuk

Reputation: 3722

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'

Upvotes: 0

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6299

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

Upvotes: 3

Related Questions