DATE_TRUNC
Reference material for the DATE_TRUNC function
Truncates a value (<expression>
) of type DATE
, TIMESTAMP
, or TIMESTAMPTZ
to the selected precision (<time_unit>
).
Syntax
Parameters
Parameter | Description |
---|---|
<time_unit> | A TEXT literal with the time unit precision to truncate to. Must be one of microsecond , millisecond , second , minute , hour , day , week , month , quarter , year , decade , century or millennium . |
<expression> | A value expression evaluating to the DATE, TIMESTAMP, or TIMESTAMPTZ value that should be truncated. |
<time_zone> | An optional TEXT literal giving a time zone name. |
Return Type
DATE if <expression>
has type DATE, TIMESTAMP if <expression>
has type TIMESTAMP, TIMESTAMPTZ if <expression>
has type TIMESTAMPTZ.
Remarks
Truncation of TIMESTAMPTZ values is performed after conversion to local time in a particular time zone.
For instance, truncation to ‘day’ produces a TIMESTAMPTZ that is midnight in that time zone.
By default, the function uses the time zone specified in the session’s time_zone
setting.
Alternatively, if the optional <time_zone>
argument is provided, the function uses that time zone.
Firebolt raises an error if the optional <time_zone>
argument is provided for an <expression>
evaluating to DATE or TIMESTAMP.
Firebolt also raises an error if one attempts to truncate a value expression of type DATE to microsecond
, millisecond
, second
, minute
, or hour
.
The DATE_TRUNC
function can be used in the PARTITION BY
clause of CREATE TABLE
commands.
Example
The following examples truncate DATE
and TIMESTAMP
values without timezones.
You can also truncate TIMESTAMPTZ
values.