Product Documentation

Appearance

All platforms

DATE_TRUNC ​

Truncate a datetime expression to the first hour, day, or month (for example), for that expression.

DATE_TRUNC('datepart', expression)

See Supported Date Parts. This function returns a TIMESTAMP or TIMESTAMPTZ data type. (If a DATE is passed as the second argument, that date is implicitly cast to TIMESTAMPTZ.)

For example, return matchday values truncated to the first of their respective months:

premdb=# SELECT matchday, DATE_TRUNC('month', matchday) 
FROM match WHERE htid = 10 ORDER BY 1 LIMIT 3;
     matchday       |     date_trunc      
---------------------+---------------------
 1999-08-14 00:00:00 | 1999-08-01 00:00:00
 1999-08-28 00:00:00 | 1999-08-01 00:00:00
 1999-09-12 00:00:00 | 1999-09-01 00:00:00
(3 rows)

For example, return the first day of a given week, then add 1 day to the result:

yellowbrick=# select date_trunc('week', '2020-10-16'::date), date_trunc('week', '2020-10-16'::date)+ 1 date_trunc_plus1;
      date_trunc       |    date_trunc_plus1    
------------------------+------------------------
 2020-10-12 00:00:00-07 | 2020-10-13 00:00:00-07
(1 row)