Links

DATE_TRUNC

Truncate a date to a given unit

Syntax

DATE_TRUNC(unit, time)

Arguments

Name
Type
Description
Default Value
unit
string
time
timestamp

Returns

timestamp

Examples

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

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 time_filter()
LIMIT 1;

Query result

unit
time
Output
'millisecond'
timestamp '2021-02-03 04:05:06.789'
timestamp '2021-02-03 04:05:06.789'
Last modified 28d ago