Links

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'
Last modified 5mo ago