Dates & time
Scalar
date_trunc()
Truncate to specified precision
Arguments & returns
4 overloadsdate_trunc(part, timestamp)
Scalar
Example
Haybarn WASM 1.5.5-rc3 · In your browser
Engine example · Run to view results.
In a query
Give your timestamps a rhythm.
Group events into calendar months with date_trunc. The same pattern works for days, weeks, and years.
Haybarn WASM 1.5.5-rc3 · In your browser
Guide recipe · Run to view results.
In other engines
BigQuerydate_trunc()
SELECT DATE_TRUNC(DATE '2008-11-10', ISOWEEK)
In DuckDBSELECT DATE_TRUNC('WEEK', CAST('2008-11-10' AS DATE))
Presto
SELECT ((DATE_ADD('week', -5, DATE_TRUNC('DAY', DATE_ADD('day', (0 - MOD((DAY_OF_WEEK(CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7, 7)), CAST(CAST(DATE_TRUNC('DAY', NOW()) AS DATE) AS TIMESTAMP)))))) AS t1SELECT ((DATE_TRUNC('DAY', CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP) + INTERVAL (0 - ((ISODOW(CAST(CAST(DATE_TRUNC('DAY', CURRENT_TIMESTAMP) AS DATE) AS TIMESTAMP)) % 7) - 1 + 7) % 7) DAY) + INTERVAL (-5) WEEK)) AS t1
SQL Serverdatetrunc()
SELECT DATETRUNC(month, foo)SELECT DATE_TRUNC('MONTH', foo)
Used in larger rewrites 1
These examples use date_trunc as one part of a larger SQL translation.
PostgreSQL
WITH dates AS (SELECT GENERATE_SERIES('2020-01-01'::DATE, '2024-01-01'::DATE, '1 day'::INTERVAL) AS date), date_table AS (SELECT DISTINCT DATE_TRUNC('MONTH', date) AS date FROM dates) SELECT * FROM date_tableWITH dates AS (SELECT UNNEST(GENERATE_SERIES(CAST('2020-01-01' AS DATE), CAST('2024-01-01' AS DATE), CAST('1 day' AS INTERVAL))) AS date), date_table AS (SELECT DISTINCT DATE_TRUNC('MONTH', date) AS date FROM dates) SELECT * FROM date_table