EXTRACT

Extracts a unit of time as a field from the given timestamp.

Syntax

EXTRACT(FIELD FROM X)

Arguments

FIELD

Type: identifier

The unit of time to extract.

The following fields are supported:

FieldDescription

YEAR

year

QUARTER

quarter

MONTH

month

WEEK

week

DAY

day

DAY_OF_MONTH

day

DAY_OF_WEEK

day of week

DOW

day of week

DAY_OF_YEAR

day of year

DOY

day of year

YEAR_OF_WEEK

year of week

YOW

year of week

HOUR

hour

MINUTE

minute

SECOND

second

X

Type: timestamp

The time to extract FIELD from.

Returns

Type: bigint

The FIELD extracted from X.


Examples

FieldXOutput

year

2012-02-22 00:00:00

2012

month

2012-02-22 00:00:00

02

day

2012-02-22 00:00:00

22

hour

2012-02-22 00:00:00

01

minute

2012-02-22 01:02:00

02

second

2012-02-22 01:00:32

21

dow

2012-02-22 01:00:32

3

dow

null

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 
        "$event_timestamp",
        EXTRACT(DAY_OF_YEAR FROM "$event_timestamp") AS event_doy
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result

$event_timestampevent_doy

2022-08-19 20:46:00.000

231

Last updated