DATE_TRUNC function - Amazon Redshift

DATE_TRUNC function

The DATE_TRUNC function truncates a timestamp expression or literal based on the date part that you specify, such as hour, week, or month. DATE_TRUNC returns the first day of the specified year, the first day of the specified month, or the Monday of the specified week.


DATE_TRUNC('datepart', timestamp)



The date part to which to truncate the timestamp value. For valid formats, see Date parts for date or timestamp functions.


A timestamp column or an expression that implicitly converts to a timestamp.

Return type



In the following example, the DATE_TRUNC function uses the 'week' date part to return the date for the Monday of each week.

select date_trunc('week', saletime), sum(pricepaid) from sales where saletime like '2008-09%' group by date_trunc('week', saletime) order by 1; date_trunc | sum ------------+------------ 2008-09-01 | 2474899.00 2008-09-08 | 2412354.00 2008-09-15 | 2364707.00 2008-09-22 | 2359351.00 2008-09-29 | 705249.00 (5 rows)