3

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?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
springcorn
  • 611
  • 2
  • 15
  • 28

1 Answers1

3

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".

Jasen
  • 11,837
  • 2
  • 30
  • 48
  • That is what I am looking for. Now I have to figure out how to let hibernate let me put that in a HQL query. It doesn't like it. – springcorn Feb 08 '19 at 01:55
  • Oh! If you'd mentioned hibernate in the original question I'd have ... run a mile. sorry I can't help with that. – Jasen Feb 08 '19 at 05:02
  • Your answer still helps, thanks! Unfortunately hql doesn't allow me to insert the "AT TIME ZONE .." clause in. Somehow I need to trick hibernate to not interpret it as a hibernate call and just insert that clause in the sql. – springcorn Feb 08 '19 at 16:41
  • maybe you can write a used defined function? – Jasen Feb 09 '19 at 02:09
  • From what I can see the only way to do it is call a sql query from hibernate, but you can't do this through HQL. HQL freaks out about the "AT TIME ZONE" clause and you get a HibernateQueryException. The problem is I have about 50 complex queries that require the extract with AT TIME ZONE and it would be incredibly trying to do them through straight sql. – springcorn Feb 12 '19 at 01:14
  • see the function above: the query to use it doesn not have `AT TIME ZONE` - for example `select date_part_in_zone( 'month', '2019-01-01 00:00 +1300', 'America/New_York' );` – Jasen Feb 13 '19 at 07:00