date_trunc

The date_trunc function can be used in multiple modes, depending on the application:

Local time preserved

The default mode for date_trunc with timestampandtz is truncating the local time and preserve the time zone. This allows you to truncate for each time zone independently:

postgres=# select dt,date_trunc('hour', dt) from times;
                dt                |            date_trunc            
----------------------------------+----------------------------------
 2014-09-15 08:15:00 @ US/Eastern | 2014-09-15 08:00:00 @ US/Eastern
 2014-09-15 09:15:00 @ US/Eastern | 2014-09-15 09:00:00 @ US/Eastern
 2014-09-15 08:20:00 @ US/Pacific | 2014-09-15 08:00:00 @ US/Pacific
 2014-09-15 09:20:00 @ US/Pacific | 2014-09-15 09:00:00 @ US/Pacific
(4 rows)

Targeted timezone

You can target a date_trunc with a time zone (similar to timestamptz truncation) by combining date_trunc with tzmove:

postgres=# select dt,date_trunc('hour', tzmove(dt, 'US/Eastern')) from times;
                dt                |            date_trunc            
----------------------------------+----------------------------------
 2014-09-15 08:15:00 @ US/Eastern | 2014-09-15 08:00:00 @ US/Eastern
 2014-09-15 09:15:00 @ US/Eastern | 2014-09-15 09:00:00 @ US/Eastern
 2014-09-15 08:20:00 @ US/Pacific | 2014-09-15 11:00:00 @ US/Eastern
 2014-09-15 09:20:00 @ US/Pacific | 2014-09-15 12:00:00 @ US/Eastern
(4 rows)

Targeted timezone and keep source time zone

The new function date_trunc_at allows a date_trunc targeted at a specific timezone (as above) but preserve the stored timezone in the result:

postgres=# select dt,date_trunc_at('day', dt, 'US/Eastern') from times;
                dt                |          date_trunc_at           
----------------------------------+----------------------------------
 2014-09-15 22:15:00 @ US/Pacific | 2014-09-15 21:00:00 @ US/Pacific
 2014-09-16 01:15:00 @ US/Eastern | 2014-09-16 00:00:00 @ US/Eastern
 2014-09-16 20:15:00 @ US/Pacific | 2014-09-15 21:00:00 @ US/Pacific
(3 rows)

Entry 1 crosses the date boundary when converted to eastern time (10:15pm -> 1:15am) so when truncated in eastern time it becomes 9/16 at 12am. This is then converted back to the source timezone (pacific) to get 9/15 at 9pm.

Entry 2 is already in eastern and past 9/16 12am so it is truncated as normal to 9/16 12am.

Entry 3 does not cross the eastern barrier (8:15pm -> 11:15pm) so it is truncated to 9/16 at 12am then converted back to pacific for 9/15 at 9pm.

Point-in-time at UTC

A point-in time at UTC truncation can be achieved with the standard ::timestamp cast along with date_trunc:

postgres=# select dt,date_trunc('hour', dt::timestamp) from times;
                dt                |     date_trunc      
----------------------------------+---------------------
 2014-09-15 08:15:00 @ US/Eastern | 2014-09-15 12:00:00
 2014-09-15 09:15:00 @ US/Eastern | 2014-09-15 13:00:00
 2014-09-15 08:20:00 @ US/Pacific | 2014-09-15 15:00:00
 2014-09-15 09:20:00 @ US/Pacific | 2014-09-15 16:00:00
(4 rows)

Point-in-time on active session timezone

A point-in-time truncation using the current session timezone is done with the standard ::timestamptz along with date_trun:

postgres=# select dt,date_trunc('hour', dt::timestamptz) from times;
                dt                |       date_trunc       
----------------------------------+------------------------
 2014-09-15 08:15:00 @ US/Eastern | 2014-09-15 08:00:00-04
 2014-09-15 09:15:00 @ US/Eastern | 2014-09-15 09:00:00-04
 2014-09-15 08:20:00 @ US/Pacific | 2014-09-15 11:00:00-04
 2014-09-15 09:20:00 @ US/Pacific | 2014-09-15 12:00:00-04
(4 rows)