DATE_TRUNC
Truncate a date to a given time unit.
Syntax
DATE_TRUNC(unit, time)
Arguments
unit
string
time
timestamp
Returns
timestamp
Examples
'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'
'test'
timestamp '2021-12-31 23:59:59.999'
null
Transformation job example
SQL
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 $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
'millisecond'
timestamp '2021-02-03 04:05:06.789'
timestamp '2021-02-03 04:05:06.789'
Last updated