<p><font face="Arial" size="2">I am trying to find out what the last full day of data for an arbitrary timezone
(generallynot the pg client's timezone). I get the max(timestamp), then would like to remove the time portion. Sounded
likea job for date_trunc, unfortunately date_trunc is not timezone aware:</font><p><font face="Arial" size="2">select
(date_trunc('day','2006-01-31 23:00:00-800' at time zone 'CST')) at time zone 'CST'</font><br /><font face="Arial"
size="2">2006-01-3122:00:00-08</font><p><font face="Arial" size="2">Since the client is in PST, the truncated date is
returnedas 2006-02-01 PST which is two hours before the desired time.</font><p><font face="Arial" size="2">extract is
slightlymore promising:</font><br /><font face="Arial" size="2">select extract(DAY from '2005-12-31 23:00:00-800' at
timezone 'CST')</font><br /><font face="Arial" size="2">1</font><p><font face="Arial" size="2">This returns the correct
day,so all I have to do is glue it back together in the right time zone:</font><br /><font face="Arial" size="2">select
to_timestamp(extract(YEARfrom '2005-12-31 23:00:00-800' at time zone 'CST')||'-'||</font><br /> <font
face="Arial"size="2">extract(MONTH from '2005-12-31 23:00:00-800' at time zone 'CST')||'-'||</font><br /><font
face="Arial"size="2"> extract(DAY from '2005-12-31 23:00:00-800' at time zone 'CST')||' 00:00:00 CST',
'YYYY-MM-DDHH:MI:SS TZ')</font><br /><font face="Arial" size="2">ERROR: "TZ"/"tz" not supported</font><p><font
face="Arial"size="2">Has anyone solved this problem before?</font><p><font face="Arial" size="2">Many
thanks,</font><p><fontface="Arial" size="2">Robert</font><p><font face="Arial" size="2">Select version()</font><br
/><fontface="Arial" size="2">PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3</font>