1

I have a column called login_timestamp, which is of type TIMESTAMP WITH TIME ZONE.

To retrieve the month for this timestamp, I would do: EXTRACT(MONTH FROM login_timestamp).

However, I would like to retrieve the month for a specific time zone (in my case, Pakistan), but can't figure out how to do that.

Ismail Khan
  • 792
  • 1
  • 7
  • 18

3 Answers3

3

Documentation for this is under Date/Time Functions and Operators. Search that page for "at time zone".

select extract(month from login_timestamp at time zone 'Asia/Karachi');

You can change the time zone for a single session or for a single transaction with set session... or set local.... For example, this changes the time zone for the current session.

set session time zone 'Asia/Karachi';
Mike Sherrill 'Cat Recall'
  • 86,743
  • 16
  • 118
  • 172
1

Use the AT TIME ZONE construct:

SELECT EXTRACT(MONTH FROM login_timestamp AT TIME ZONE '-5');

-5 is the constant offset for Pakistan.

Details:

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
0

Try applying AT TIME ZONE. Demo

select extract(month from cast ('2017-07-01 01:00+03' as TIMESTAMP WITH TIME ZONE) AT TIME ZONE '+08') as monthNo

returns 

    monthno
1   6
Serg
  • 20,397
  • 5
  • 20
  • 44