OverviewQuickstartsHow To GuidesReferenceArticlesSupport

EXTRACT_TIMESTAMP

Convert a date or timestamp string to a date or timestamp, autodetecting the date format.

Syntax

EXTRACT_TIMESTAMP(datetime)

Arguments

Name
Type
Description
Default Value

datetime

string

Convert a date or timestamp string to a date or timestamp, autodetecting the date format.

Returns

timestamp

Examples

datetime
Output

'2017-09-25 22:11:00'

timestamp '2017-09-25 22:11:00'

'2017-09-25T22:11:12Z'

timestamp '2017-09-25 22:11:12'

'2017-09-25T22:11:12.1Z'

timestamp '2017-09-25 22:11:12.100'

'2017-09-25T22:11:12.12Z'

timestamp '2017-09-25 22:11:12.120'

'2017-09-25T22:11:12.123Z'

timestamp '2017-09-25 22:11:12.123'

'2018-03-19'

timestamp '2018-03-19 00:00:00'

'2018_Mar_21_17:59:05.689'

timestamp '2018-03-21 17:59:05.689'

'2018_Mar_21'

timestamp '2018-03-21 00:00:00'

'2018_Mar_21_00:59:05.689 America/Los_Angeles'

timestamp '2018-03-21 07:59:05.689'

'March 21, 2018 00:59:05.689 America/Los_Angeles'

timestamp '2018-03-21 07:59:05.689'

'03/21/2020 00:59:05.689'

timestamp '2020-03-21 00:59:05.689'

'03/11/2020 00:59:05.689 America/Los_Angeles'

timestamp '2020-03-11 07:59:05.689'

'03/11/2020 00:59:05.689 America/Los_Angeles'

timestamp '2020-03-11 07:59:05.689'

'21/11/2020 00:59:05.689 America/Los_Angeles'

timestamp '2020-11-21 08:59:05.689'

'https://www.zillow.com/homedetails/33-Rohde-Ave-St-Augustine-FL-32084/2099072901_zpid/'

timestamp '2099-07-29 01:00:00'

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 datetime,
        EXTRACT_TIMESTAMP(datetime) AS Output
    FROM default_glue_catalog.upsolver_samples.orders_raw_data
    LET datetime = '2017-09-25 22:11:00'
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result

datetime
Output

'2017-09-25 22:11:00'

timestamp '2017-09-25 22:11:00'

Last updated