DATE_TRUNC
Truncate a date to a given unit
DATE_TRUNC(unit, time)
Name | Type | Description | Default Value |
|---|---|---|---|
unit | string | | |
time | timestamp | | |
timestamp
unit | time | Output |
|---|---|---|
'millisecond' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-03 04:05:06.789' |
'second' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-03 04:05:06' |
'minute' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-03 04:05:00' |
'hour' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-03 04:00:00' |
'day' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-03 00:00:00' |
'week' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-01 00:00:00' |
'month' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-01 00:00:00' |
'quarter' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-01-01 00:00:00' |
'year' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-01-01 00:00:00' |
'millisecond' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-12-31 23:59:59.999' |
'second' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-12-31 23:59:59' |
'minute' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-12-31 23:59:00' |
'hour' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-12-31 23:00:00' |
'day' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-12-31 00:00:00' |
'week' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-12-27 00:00:00' |
'month' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-12-01 00:00:00' |
'quarter' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-10-01 00:00:00' |
'year' | timestamp '2021-12-31 23:59:59.999' | timestamp '2021-01-01 00:00:00' |
'bla' | timestamp '2021-12-31 23:59:59.999' | null |
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data MAP_COLUMNS_BY_NAME
SELECT unit, time,
DATE_TRUNC(unit, time) AS Output
FROM default_glue_catalog.upsolver_samples.orders_raw_data
LET unit = 'millisecond',
time = timestamp '2021-02-03 04:05:06.789'
WHERE time_filter()
LIMIT 1;
unit | time | Output |
|---|---|---|
'millisecond' | timestamp '2021-02-03 04:05:06.789' | timestamp '2021-02-03 04:05:06.789' |
Last modified 28d ago