SUBTRACT_TIME_ZONE_OFFSET

Subtracts a timezone offset from a date.

The timezone offset can be any standard format accepted by Java's ZoneId.of method (America/New_York, +02:00, etc.)

Syntax

SUBTRACT_TIME_ZONE_OFFSET(time, zone)

Arguments

Name	Type	Description	Default Value
time	timestamp
zone	string

Returns

timestamp

---

Examples

time	zone	Output
timestamp '2018-03-10 08:55:12.456'	'+02:00'	timestamp '2018-03-10 06:55:12.456'
timestamp '2018-03-10 08:55:12.456'	'America/New_York'	timestamp '2018-03-10 13:55:12.456'
timestamp '2018-03-10 08:55:12.456'	''	null
timestamp '2018-03-10 08:55:12.456'	'Invalid Zone Id'	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 time, zone,
        SUBTRACT_TIME_ZONE_OFFSET(time, zone) AS Output
    FROM default_glue_catalog.upsolver_samples.orders_raw_data
    LET time = timestamp '2018-03-10 08:55:12.456',
        zone = '+02:00'
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result

time	zone	Output
timestamp '2018-03-10 08:55:12.456'	'+02:00'	timestamp '2018-03-10 06:55:12.456'