Working with datetime objects

Datetime objects are created from string representations — e.g. "2020-04-01" or "Apr 1, 2020 12:00" — in your data. If the string value is in an automatically recognized datetime pattern, you can perform the conversion using either of these ATL functions:

Note that when you pass a string in an automatically recognized pattern into a function or expression that expects a datetime object, Studio implicitly creates a datetime object. This means converting with a function may be unnecessary.

Similarly, if your CSV data file contains a column of string values in an automatically recognized datetime pattern, the value variable created for that column (in "Describe Each Row" and "Describe Row in Context" projects) will return a datetime object. You can check the variable type in your project's Variables View — it will show DATETIME rather than STRING.

If your string is NOT in an automatically recognized pattern, you can convert that string to a datetime object by using the parseDateTime function. To do this, specify the pattern of the input string in the second parameter.

[[
stringValue = '02/04/2020';
dateTimeObject = parseDateTime(stringValue, 'dd/MM/yyyy');
]]

You can then pass the created object into another datetime function. For example:

[[
stringValue = '02/04/2020';
dateTimeObject = parseDateTime(stringValue, 'dd/MM/yyyy');
formatDateTime(dateTimeObject, 'd MMMM yyyy')
]]

By default, Studio prints datetime objects by applying this format pattern:

MMM d, yyyy HH:mm:ss a

Here are some examples:

To change the display format, use the formatDateTime function and specify a pattern.

[[myDate = parseDateTime('2021-10-31')]]

[[
myDate = parseDateTime('2021-10-31');
formatDateTime(myDate, 'dd.MM.yyyy')
]]

[[
myDate = parseDateTime('2021-10-31');
formatDateTime(myDate,'EEEE, dd MMMM yyyy')
]]

[[
myDateTime = parseDateTime('2021-10-31T14:00');
formatDateTime(myDateTime,'d/MM/yyyy HH:mm')
]]

[[myDateTime = parseDateTime('2021-10-31T14:00-05:00')]]

[[
myDateTime = parseDateTime('2021-10-31T14:00-05:00');
formatDateTime(myDateTime, "d MMM, yyyy HH:mm:ss XXX")
]]

[[myDateTime = parseDateTime('2021-10-31T14:00z[America/New_York]')]]

[[
myDateTime = parseDateTime('2021-10-31T14:00z[America/New_York]');
formatDateTime(myDateTime, "d MMM, yyyy HH:mm:ss a VV")
]]

[[
myDateTime = parseDateTime('2021-10-31T14:00-05:00');
getTimeZone(myDateTime)
]]

[[
myDateTime = parseDateTime('2021-10-31T14:00z[America/New_York]');
getTimeZone(myDateTime)
]]

Studio recognizes and converts a string to a datetime object when it matches one of the following patterns:

PATTERN 1 — DATE-ONLY VALUES

yyyy-MM-dd

PATTERN 2 — TIME-ONLY VALUES

HH:mm:ss.SSSSSSSSS

PATTERN 3 — DATE + TIME VALUES

yyyy-MM-ddTHH:mm:ss.SSSSSSSSS

PATTERN 4 — DATE + TIME VALUES WITH A TIME OFFSET

yyyy-MM-ddTHH:mm:ss.SSSSSSSSSXXX

PATTERN 5 — DATE + TIME VALUES WITH GEOGRAPHIC TIMEZONE ID

yyyy-MM-ddTHH:mm:ss.SSSSSSSSSXXX[zone-ID]

IMPORTANT: The offset and timezone should be equivalent. If not, Studio first applies the offset to the time to calculate the time instant (the UTC time), then translates that to the equivalent for the given timezone. For example:

Remember that the time 10:00+06:00 represents a local time six hours ahead of UTC. Therefore, 10:00 in this local time translates to 04:00 UTC. The Paris equivalent is 05:00 because Paris is one hour ahead of UTC.

PATTERN KEY

EXAMPLES OF STRINGS IN RECOGNIZED PATTERNS

As with numbers, you can compare dates and times by writing ATL statements with standard logical operators (==, !=, >, >=, <, <=). One datetime value is considered less than another if it occurs earlier. For example, a date in May 2021 is considered less than a date in June 2021. Similarly, if two times are on the same day, the earlier time is the lesser.

When a string value is in an automatically recognized pattern, Studio converts it to a datetime object for the comparison.

[[
dateTime1 = parseDateTime('06/05/19 12:30', 'dd/MM/yy HH:mm');
dateTime2 = '2019-05-06T12:30';
dateTime1 == dateTime2
]]

Both dateTime1 and dateTime2 return a datetime object. dateTime1 is created by using the parseDateTime function, but this is not required for dateTime2 because the string '2019-05-06T12:30' is an automatically recognized datetime pattern.

[[Name]] is [[if(BirthDate < '1950-01-01'){prehistoric} elseif(BirthDate < '2000-01-01'){middle-aged} else{still young}]].

[[
dateTime1 = parseDateTime('2021-12-12T08:00:00');
dateTime2 = parseDateTime('2021-12-11T10:00:00');
dateTime1 < dateTime2
]]

[[
dateTime1 = parseDateTime('2021-12-12');
dateTime2 = parseDateTime('2021-12-12T00:00:00');
dateTime1 == dateTime2
]]

[[
dateTime1 = parseDateTime('05:00');
dateTime2 = parseDateTime('2021-12-12T05:00');
dateTime1 == dateTime2
]]

[[
dateTime1 = parseDateTime('09:41:25');
dateTime2 = parseDateTime('2000-01-01');
dateTime1 < dateTime2
]]

[[
dateTime1 = '2021-07-04T18:00+01:00[Europe/London]';
dateTime2 = '2021-07-04T13:00-04:00[America/New_York]';
dateTime1 == dateTime2
]]

[[
dateTime1 = '2021-07-04T18:00+01:00[Europe/London]';
dateTime2 = '2021-07-04T13:00-04:00[America/New_York]';
dateTimeDifference(dateTime1,dateTime2) == 0
]]

You can use the + and - operators to add or subtract a duration (in seconds) from a datetime object. The duration can be a number or an expression that evaluates to a number. Examples of both are shown below.

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
dateTime + 60
]]

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
dateTime - 60
]]

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
dateTime + (60 * 60 * 24)        
]]

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
dateTime - (60 * 60 * 24 * 5)        
]]

Alternatively, use parseDuration, which takes a string representing a duration and returns a number expressing that duration in seconds.

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
dateTime + parseDuration('13 days','d')
]]

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
dateTime - parseDuration('10 weeks','w')
]]

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
dateTime + parseDuration('1 year, 1 day and 1 hour','ydh')
]]

You can also use addToDateTime or subtractFromDateTime.

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
addToDateTime(dateTime,'1 year 1 day and 1 hour','ydh')
]]

[[
dateTime = parseDateTime('2021-12-25T08:00:00');
subtractFromDateTime(dateTime,'1 year 1 day and 1 hour','ydh')
]]

Studio includes a large number of datetime functions, many of which are not covered in this topic. These simplify tasks such as: