DATE_TRUNC

Truncate a date to a given time unit.

Syntax

DATE_TRUNC(unit, time)

Arguments

NameTypeDescriptionDefault Value

unit

string

time

timestamp

Returns

timestamp


Examples

unittimeOutput

'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

unittimeOutput

'millisecond'

timestamp '2021-02-03 04:05:06.789'

timestamp '2021-02-03 04:05:06.789'

Last updated