From: | "Davidson, Robert" <robdavid(at)amazon(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Getting yyyy-mm-dd 00:00:00 in an arbitrary time zone |
Date: | 2006-03-04 00:13:04 |
Message-ID: | D224B13DA625924883A3B024CD6608F001A6BB66@exchg-sea5-03.ant.amazon.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I am trying to find out what the last full day of data for an arbitrary timezone (generally not the pg client's timezone). I get the max(timestamp), then would like to remove the time portion. Sounded like a job for date_trunc, unfortunately date_trunc is not timezone aware:
select (date_trunc('day', '2006-01-31 23:00:00-800' at time zone 'CST')) at time zone 'CST'
2006-01-31 22:00:00-08
Since the client is in PST, the truncated date is returned as 2006-02-01 PST which is two hours before the desired time.
extract is slightly more promising:
select extract(DAY from '2005-12-31 23:00:00-800' at time zone 'CST')
1
This returns the correct day, so all I have to do is glue it back together in the right time zone:
select to_timestamp(extract(YEAR from '2005-12-31 23:00:00-800' at time zone 'CST')||'-'||
extract(MONTH from '2005-12-31 23:00:00-800' at time zone 'CST')||'-'||
extract(DAY from '2005-12-31 23:00:00-800' at time zone 'CST')||' 00:00:00 CST', 'YYYY-MM-DD HH:MI:SS TZ')
ERROR: "TZ"/"tz" not supported
Has anyone solved this problem before?
Many thanks,
Robert
Select version()
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-03-04 00:32:25 | Re: Getting yyyy-mm-dd 00:00:00 in an arbitrary time zone |
Previous Message | Mark Dilger | 2006-03-03 20:25:58 | Re: [SQL] Interval subtracting |