PostgreSQL extract function in respect to timezones

Question

I use the PostgreSQL extract function to pull out month, quarter, year like this:

select extract (month from adate) from atable

adate is a timestamp with time zone.

The problem I have is that I find this function completely ambivalent of timezone and I am dealing with a database with data that varies by timezone. So in some cases I need the result in respect to pacific timezone in other cases I need the result in respect to EST or CST, etc.

Is there a way to get the month/quarter/year of the date in respect to a specific timezone?

Answer 1

Basically what you do is use the AT TIME ZONE operator co convert the TIMESTAMP WITH TIMEZONE into a TIMESTAMP WITHOUT TIMEZONE and then apply the date_part (AKA extract) operation to that

If you want a specific time zone:

  select extract(month from adate AT TIME ZONE 'CST' ) from atable;

If you want a specific location: this will follow historical daylight-saving rules (and other statutory time zone changes) for past dates, and assume the current rules for future dates.

  select extract(month from adate AT TIME ZONE 'America/Detroit' ) from atable;

maybe a used defined function will help with hibernate.

 CREATE OR REPLACE FUNCTION date_part_in_zone
     ( part text, instant timestamptz, zone text) 
     returns double precision language sql as 
     'SELECT date_part(part, instant AT TIME ZONE zone)';

date_part above is one of the functions behind "extract".

Answer 2

I found I had to use the AT TIME ZONE twice. Since my date value was stored without a timezone as UTC, and I wanted to convert to my local timezone I did this:

SELECT EXTRACT(month from adate AT TIME ZONE 'UTC' AT TIME ZONE 'CST')