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
  • 842
  • 2
  • 8
  • 20

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'
  • 91,602
  • 17
  • 122
  • 185
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
  • 605,456
  • 145
  • 1,078
  • 1,228
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
  • 22,285
  • 5
  • 21
  • 48