I was loading timestamp data with the Reltio Data Loader and learned a few tricks that I thought I'd share….
The Reltio Data Modeler uses Timestamp to define date/time attributes. There are some use cases where Timestamp means just the time. But this is not one of them. A Timestamp in Reltio means a date/time. This is of course different from a Date attribute which will only have Date information without the time.
Epoch Time
When importing date/time in Reltio, you can always use millisecond Epoch time. Please note that this is different from Epoch time as the number will be larger. For example, Feb 1, 2024 at 10:21:40 will convert to a value close to 1706811700184. If your converter converts this date to something like 1706485855 you are using the wrong conversion (notice the different number of digits)
Documented Date Formats
Reltio documents valid date formats in a couple of places (Docs), (Support Portal). The both links present the following formats:
Much of this is familiar if you have worked with date/time information in the past, but here are the descriptions just in case:
-
yyyy - the four digit year
-
MM - the two digit month
-
dd - the two digit day
-
HH - the two digit hour
-
mm - the two digit minutes
-
ss - the two digit seconds
-
SSS - subseconds. It’s written here in milliseconds, but the Java spec actually says the length is undefined. As in it could be longer. For the purposes of MDM, seconds and subseconds are very rarely used and I’m not going to go deeper into it here
If you remember anything about this blog, make it these two things:
-
Pay attention to the periods and colons in these formats! I spent a lot of time wondering why my data would not load before I noticed that I had a colon where I should have had a period.
-
The quotes must be straight quotes, not curly quotes. You might have to turn off curly quotes in your editor of choice to get the straight quotes. More on the quote mark and what it means later in this article.
The ‘Z’ vs ‘ZZ’ is much different than it looks. I was familiar with the use of ‘Z’ to denote Zulu or UTC. However, the ‘ZZ’ means ‘enter the time zone offset to UTC’. So use +0100 for Central European Time or -0800 for United States Pacific Time. Note that there is NO SPACE between the offset and the time element before it with the out of the box formats.
Take a look at these examples to see what these formats should look like when you enter date/time data in Reltio.
The description column identifies the time format being used and the birthdatetime column contains the data that is mapped to the Timestamp attribute in Reltio. Note the use of periods vs colons and how the ZZ has been set to the time zone offset without a space. Additionally, note that the format contains the T with quote marks while the data contains the T without the quote marks.
We have now defined the out-of-the-box time/date formats and what the input should look like for each. For any particular data set you will probably only need to use one of these formats. But you can mix and match the out-of-the-box formats. I used the CSV file illustrated in the screenshot above to load all the data with Data Loader in one pass. The resulting data looks like this in the Reltio UI:
Note that the three formats that don't declare the time zone all assume the time given to Reltio was UTC (15:35 UTC is 8:35 AM Pacific Time for a date where the US is in daylight savings time. The conversion is to Pacific time because my browser is set to use Pacific Time). So if the timezone of the time is important in your implementation, you'll need to do the conversion to UTC or use a format that allows the declaration of time zone.
Custom Time Formats
Reltio Data Loader also allows you to create custom time formats, for use cases where the existing formats can't be used or you don't want to convert your existing data. Under the covers, Reltio is using the Java SimpleDateFormat function, so any date formats that work with that Java function should be allowed. In the Data Loader, this is done with a field that appears in the mapper only for attributes of type Timestamp. Expose this field by clicking the gear icon. Here is an example of a custom date/time format:
Let's review the input data format. The yyyy-dd-MM part of the format uses the elements that we defined above to create a format with a European locale by placing the date before the month. Then there is a 'T'. The quote marks indicate that the T is a literal in the data. Then HH:MMZZ which we define above. The input data for this entity was 1962-20-04T04:35+0100. To write it out, this means April 20, 1962 at 4:35 AM Central European Time. When imported to Reltio, it is displayed as follows:
UTC + 1 hour is 8 hours ahead of Pacific Time in April. So 5:35 in the morning of the 20th is 7:35 PM on the 19th in Pacific Time.
This is just one example of a custom format. Again, any format supported by the Java SimpleDateFormat should work.
Summary
In this blog post, we discussed using Timestamps in the Reltio Data Loader. Using timestamps does not have to be difficult as several formats are supported. Additionally, custom timestamp formats are supported when using the Reltio Data Loader.