DATE_TRUNC

Truncate a date to a given time 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'
'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

unit	time	Output
'millisecond'	timestamp '2021-02-03 04:05:06.789'	timestamp '2021-02-03 04:05:06.789'