PARSE_DATETIME

Converts a time field into a timestamp based on the specified Java format syntax. This function allows the parsing of a datetime string into a timestamp by using the provided Java date format pattern, following the Java DateTimeFormatter syntax.

Syntax

PARSE_DATETIME(time_field, format_pattern)

Arguments

Argument	Type	Description	Default Value
time_field	string	The time field to be parsed
format_pattern	string	The format pattern in Java syntax to parse the datetime

Returns

• timestamp

Examples

The following table illustrates how PARSE_DATETIME can be used with different input strings and format patterns:

Examples

input	format	Output
'2017-09-25 22:11:00'	'yyyy-MM-dd HH:mm:ss'	timestamp '2017-09-25 22:11:00'
'2018-03-19'	'yyyy-MM-dd'	timestamp '2018-03-19 00:00:00'
'2018_Mar_21'	'yyyy_MMM_dd'	timestamp '2018-03-21 00:00:00'
'2018_Mar_21_17:59:05.689'	'yyyy_MMM_dd_HH:mm:ss.SSS'	timestamp '2018-03-21 17:59:05.689'
'2012/02/22 12:34:56'	'Y/MM/d HH:mm:ss'	timestamp '2012-02-22 12:34:56'
'01-06-2021'	'dd-MM-YYYY'	timestamp '2021-06-01 00:00:00'
'01-06-2021'	'MM-dd-YYYY'	timestamp '2021-01-06 00:00:00'
'2021-05-13 19:02:57.415006+01:00'	'YYYY-MM-dd HH:mm:ss.SSSSSSZ'	timestamp '2021-05-13 18:02:57.415'

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 input, format,
        PARSE_DATETIME(input, format) AS Output
    FROM default_glue_catalog.upsolver_samples.orders_raw_data
    LET input = '2017-09-25 22:11:00',
        format = 'yyyy-MM-dd HH:mm:ss'
    WHERE time_filter()
    LIMIT 1;

Query result

input	format	Output
'2017-09-25 22:11:00'	'yyyy-MM-dd HH:mm:ss'	timestamp '2017-09-25 22:11:00'