header
Solved

How to convert epoch time to date?

  • 3 February 2021
  • 2 replies
  • 25 views
Jef Vanlaer
Rank
Userlevel 5
Badge

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?

 

icon

Best answer by Jef Vanlaer 5 February 2021, 09:09

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.

View original

2 replies

sai_ram
Userlevel 7
Badge

@Jef Vanlaer Thanks for writing your query here. It's been long time I heard a question on epoch format. I will check and let you know. 

Jef Vanlaer
Rank
Userlevel 5
Badge

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.

Reply