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:

Field	Description
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

Field	X	Output
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_timestamp	event_doy
2022-08-19 20:46:00.000	231