DATE_TRUNC(precision, source) truncates the date/time value source to the start of the time unit specified by precision.
Syntax
DATE_TRUNC(precision, source)
Parameters
Parameters |
Note |
|---|---|
precision |
String. Specifies the unit of time for truncation. Case-insensitive matching is performed. For specific valid values, see the table below. |
source |
The date or time value to be truncated. DATETIME, DATE Return by the original type; other types are returned as DATETIME Process and return DATETIME. |
Supported precision values
Valid values |
Truncated at |
|---|---|
microseconds |
microsecond |
milliseconds |
Millisecond |
second |
Seconds |
minute |
subdivision |
hour |
hour |
day |
day |
week |
Week (ISO week, Monday as the start of the week) |
month |
Month |
quarter |
Quarter |
year |
year |
decade |
ten years |
century |
century |
millennium |
thousand years |
Return value
Returns a truncated date/time value of the same type as source. If source is not a date/time type, it is first interpreted as a DATETIME and then returned as a DATETIME.
Considerations
- If the value of
precisionis not in the preceding list (for example,weeksor the plural form ofmonths), it will be treated as an invalid time unit and an error will be returned. - The
precisionparameter is case-insensitive (for example,MONTH,Month, andmonthare equivalent).
Examples
Truncate the timestamp to the month.
obclient> SELECT DATE_TRUNC('month', TIMESTAMP '2026-03-24 14:30:00') AS d;The return result is as follows:
+---------------------+ | d | +---------------------+ | 2026-03-01 00:00:00 | +---------------------+ 1 row in setTruncated to the beginning of the quarter.
obclient> SELECT DATE_TRUNC('quarter', TIMESTAMP '2026-08-15 09:00:00') AS d;The return result is as follows:
+---------------------+ | d | +---------------------+ | 2026-07-01 00:00:00 | +---------------------+ 1 row in setTruncated to the start of the ISO week (Monday).
obclient> SELECT DATE_TRUNC('week', TIMESTAMP '2026-04-29 10:00:00') AS d;The return result is as follows:
+---------------------+ | d | +---------------------+ | 2026-04-27 00:00:00 | +---------------------+ 1 row in setThe
DATEtype is returned as is (it is not coerced toDATETIME).obclient> SELECT DATE_TRUNC('month', DATE '2026-03-24') AS d;The return result is as follows:
+------------+ | d | +------------+ | 2026-03-01 | +------------+ 1 row in set