Use these functions to work with dates.

Overview

Tamr uses:

• the ISO 8601 standard.
• the SimpleDateFormat from Java for formatting date and time pattern strings.

Convert Timestamps to ISO 8601

The following two functions allow you to convert timestamps in a variety of formats to the ISO 8601 date format. Both functions accept a list of formats for conversion, allowing the conversion of multiple formats present in a single attribute to ISO 8601.

datetime_to_iso

If your timestamp is in one datetime string, this function allows you to convert to ISO 8601 format. Note that you do not have to specify the format down to nanoseconds. If you do, and the datetime attribute does not include them, they are filled with zeroes. The format array is case-sensitive.
An example for conversion from the EU formats is:

datetime_to_iso(get(<datetime_attribute>, 0), array('yyyy-MM-dd-:HH:mm:ss.n','yyyy-MM-dd :HH:mm:ss.n'))

date_and_time_to_iso

If your timestamp is separated into two columns for date and time, you can use this function to convert them to an ISO 8601 timestamp.

date_and_time_to_iso(get(<date_attribute>, 0), get(<time_attribute>,0), array('dd MM yyyy:HH:mm', 'dd/MM/yyyy:HH:mm'))

📘

Datetime Formats

Date and Time: Date and time are separated by :, such as dd/MM/yyyy:HH:mm.

Where:

• MMM indicates a three-letter month, such as JAN.
• MM indicates a two-digit month, such as 01.

Working with Dates in ISO 8601 Format

Other functions can act on strings that are in ISO 8601 format. These are:

iso_datetime_add

Add or subtract time from a date. Valid units are: SEC (seconds), MIN (minutes), HRS (hours), DAY (days), WKS (weeks), YRS (years), and MTH (months). Note that durations of Months and Years must be whole numbers, since months and years, such as leap years, can vary in length.
For example, to add four hours to a date:

iso_datetime_add(<date_attribute>, 4.0, 'hrs') AS <date_attribute>

To get the day before:

iso_datetime_add(<date_attribute>, -1.0, 'day') AS <date_attribute>

iso_difference_days

Get the number of days between two dates in ISO format. Returns a negative number if <date1_attribute> is the later date of the two.

iso_difference_days(<date1_attribute>,<date2_attribute>) AS <duration_attribute>

duration_to_iso

Convert a number to ISO duration format. Valid units are SEC (seconds), MIN (minutes), HRS (hours), DAY (days), and WKS (weeks).

duration_to_iso(<duration_attribute>, 'DAY', true) AS <duration_attribute>

dayofmonth

Calaculate the day of month (1 to 31) of a date in ISO format.

dayofmonth(<date_attribute>) AS <date_attribute>

Dealing with Two-Digit Years

A known issue is that the dayofmonth function is hampered by a quirk of Java 8: any two-digit year passed to the function is treated as a year in the range 2000-2099. A recommended workaround is as follows.

In the following example, Date is the name of the column or attribute that has the date string being converted. First, we convert the Date to the ISO 8601 standard using the function datetime_to_iso. Next, we use a CASE statement to detect any years into the future, such as 2080, to a year in the past, such as 1980. Here, we use the year 2030 as a cutoff, but this can be customized. You could also use the current year as the cutoff point by using the function runtime_date.

// This SELECT converts Date into the ISO standard. You need to match the array formats to match your input data.
SELECT *, datetime_to_iso(Date, array('dd-MMM-yy')) AS my_iso_date;

// This CASE statement checks the year of my_iso_date and converts from  a 20xx year to a 19xx year if the year is greater than 2030. The year 2030 is an example and should be updated for your use case. 
SELECT *,
CASE WHEN year(my_iso_date) > 2030
THEN iso_datetime_add(my_iso_date, -100.0, 'YRS')
ELSE my_iso_date
END AS my_iso_date;