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
