I'm ingesting data from S3, which contains dates in epoch format (unix timestamp). I was planning to use the DATE_ADD function to convert this to an actual date, but that only seems to function on a Date attribute with a fixed interval, while I have the opposite situation (fixed date: 1970-01-01, interval attribute, being the epoch time). Does anyone know how I can make this conversion, either in Rules Engine or via a Calculated Field on the object I write the data to?
Best answer by Jef Vanlaer
Thanks to some great help during the EMEA office hours, I was able to work around this. Here's the steps I took:
- Add a custom field "EPOCH_START_DATE" to the target object, defaulting to 1970-01-01
- Import the epoch times as numbers via the Rules Engine
- Create an additional rule to:
- Convert epoch time to days since 1970-01-01 (Days_since_1970 = epoch_time/60/60/24)
- Use Add / Subtract Date function to calculate the date as follows:
Add / Subtract Date(EPOCH_START_DATE,+,Days_since_1970,Days)
- Write the dates to the target object
Finally, I linked the rule importing the data and the conversion rule in a rule chain, so they will always be executed together and in the right order.