ETL: ToDate

Convert an input string to a datetime data type based on a specified format pattern. Use TODATE for date parsing in Integrate.io ETL pipelines.

Description

Converts input, based on format, to datetime data type.

Syntax

• ToDate(milliseconds)
• ToDate(datetime_string)
• ToDate(custom_string, format)
• ToDate(custom_string, format, timezone)

Arguments

• milliseconds - long. The number of milliseconds from 1970-01-01T00:00:00.000Z (similar to Unix time except that Unix is the number of seconds instead of milliseconds.)
• datetime_string - string expression in ISO 8601 format. If the value of the expression is not in the ISO 8601 format, an exception is thrown.
• custom_string - datetime value in a string expression in a custom format
• format - format of the custom string. See below for formatting information.
• timezone - time zone information. Use an IANA (tz database) region identifier in Area/City form, such as Europe/London or America/Chicago. See the List of tz database time zones on Wikipedia for valid identifiers.

Pick the region identifier for your location and let ToDate handle daylight saving using the tz database. Never pick a zone because its offset happens to match your clock today. A zone chosen by offset, or a different region that shares your current offset, will be wrong for part or all of the year.

Format strings

Symbol	Meaning	Presentation	Examples
G	era	text	AD
C	century of era (>=0)	number	20
Y	year of era (>=0)	year	1996
x	weekyear	year	1996
w	week of weekyear	number	27
e	day of week	number	2
E	day of week	text	Tuesday; Tue
y	year	year	1996
D	day of year	number	189
M	month of year	month	July; Jul; 07
d	day of month	number	10
a	halfday of day	text	PM
K	hour of halfday (0~11)	number	0
h	clockhour of halfday (1~12)	number	12
H	hour of day (0~23)	number	0
k	clockhour of day (1~24)	number	24
m	minute of hour	number	30
s	second of minute	number	55
S	fraction of second	number	978
z	time zone	text	Pacific Standard Time; PST
Z	time zone offset/id	zone	-0800; -08:00; America/Los_Angeles
\‘	escape character	for a-z/A-Z	T
\’\‘	single quote	literal single quote	’

Based on https://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html

Examples

• ToDate('2013-10-17T23:59:54.432Z') returns datetime value 2013-10-17T23:59:54.432Z
• ToDate(0) returns datetime value 1970-01-01T00:00:00.000Z
• ToDate(1414877400 * 1000) returns datetime value 2014-11-01T21:30:00.000Z (takes the Unix timestamp of 1414877400 which is in seconds and multiplies it by 1000 to generate the number of milliseconds that the ToDate function requires)
• ToDate(1585195200000) returns datetime value 2020-03-26T04:00:00.000Z. (When run in X-Console, explicitly cast that number as a Long datatype like this: ToDate(1585195200000L))
• ToDate('10/17/2013 23:59:54','MM/dd/yyyy HH:mm:ss') returns datetime value 2013-10-17T23:59:54.000Z
• ToDate('10/17/2013 23:59:54','MM/dd/yyyy HH:mm:ss','+01:00') returns datetime value 2013-10-17T23:59:54.000+01:00
• ToDate('Tue Jul 02 09:18:52 +0000 2013','EEE MMM dd HH:mm:ss Z yyyy') returns datetime value 2013-07-02T09:18:52.000Z (This is the datetime format used in Twitter stream).
• ToDate('2019-03-17T12:11:04 +06:00', 'yyyy-MM-dd\'T\'HH:mm:ss Z') returns 2019-03-17T06:11:04.000Z

Return value datatype

datetime

Notes

If format or timezone are null, an exception will be thrown.

Impact of null value

If milliseconds, iso8601_string or custom_string are null, returns null.