DATE_TRUNC (legacy)
You are looking at legacy documentation for Firebolt’s deprecated date and timestamp type functions. New types were introduced in DB version 3.19 under the names
PGDATEandTIMESTAMPNTZ, and made generally available in DB version 3.22.If you worked with Firebolt before DB version 3.22, you might still be using the legacy date and timestamp types. Determine which types you are using by executing the query
SELECT EXTRACT(CENTURY FROM DATE '2023-03-16');. If this query returns an error, you are still using the legacy date and timestamp types and can continue with this documentation, or find instructions to use the new types here. If this query returns a result, you are already using the redesigned date and timestamp types and can use the DATE_TRUNC function instead.
Truncates a value (<expression>) of type DATE, TIMESTAMP, or TIMESTAMPTZ to the selected precision (<time_unit>).
Syntax
DATE_TRUNC(<time_unit>, <expression> [, <time_zone> ])
Parameters
| Parameter | Description | Supported input types |
|---|---|---|
<time_unit> | The time unit precision to truncate to. | 'microseconds', 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', 'millennium' (quoted) |
<expression> | An expression to be truncated. | DATE, TIMESTAMP, TIMESTAMPTZ |
<time_zone> | Optional, the time zone to use for TIMESTAMPTZ values. | A string literal such as 'Europe/Berlin' |
Return Types
Same as the input data type (<time_unit>)
Remarks
When the <expression> value is of type TIMESTAMPTZ, truncation is performed after converting the value from Unix time 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, DATE_TRUNC uses the time zone specified in the session’s <time_zone> setting, but the optional <time_zone> argument can be provided to use a different time zone.
Firebolt throws an error if the <time_zone> argument is provided for a source of type DATE or TIMESTAMP.
The DATE_TRUNC function can be used in the PARTITION BY clause of CREATE TABLE commands.
CREATE DIMENSION TABLE test (
d DATE,
t TIMESTAMP
)
PARTITION BY DATE_TRUNC('month', d), DATE_TRUNC('hour', t);
Example
The example below uses a table date_test with the columns and values below.
| Cat | sale_datetime |
|---|---|
| a | 2017-06-15 09:34:21 |
| b | 2014-01-15 12:14:46 |
| c | 1999-09-15 11:33:21 |
SELECT DATE_TRUNC('century', DATE '1996-09-03'); --> 1901-01-01
SELECT DATE_TRUNC('hour', TIMESTAMP '1996-09-03 11:19:42.123'); --> 1996-09-03 11:00:00
SET time_zone = 'US/Pacific';
SELECT DATE_TRUNC('week', TIMESTAMPTZ '1996-09-03 11:19:42.123 Europe/Berlin'); --> 1996-09-02 00:00:00-07
SELECT DATE_TRUNC('week', TIMESTAMPTZ '1996-09-03 11:19:42.123 Europe/Berlin', 'Europe/Berlin'); --> 1996-09-01 15:00:00-07
+———-+———————+———————+———————+———————+ | category | sale_datetime | MINUTE | HOUR | DAY | | a | 2017-06-15 09:34:21 | 2017-06-15 09:34:00 | 2017-06-15 09:00:00 | 2017-06-15 00:00:00 | | b | 2014-01-15 12:14:46 | 2014-01-15 12:14:00 | 2014-01-15 12:00:00 | 2014-01-15 00:00:00 | | c | 1999-09-15 11:33:21 | 1999-09-15 11:33:00 | 1999-09-15 11:00:00 | 1999-09-15 00:00:00 | +———-+———————+———————+———————+———————+ ```