Skip to main content
Solved

How to convert epoch time to date?

  • February 3, 2021
  • 2 replies
  • 2456 views
Jef Vanlaer
Forum|alt.badge.img+2

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.

mikewayner

2 replies

sai_ram
Forum|alt.badge.img+1
  • sai_ram
  • Expert ⭐️⭐️
  • February 3, 2021

@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
Forum|alt.badge.img+2
  • Jef Vanlaer
  • Author
  • Helper ⭐️⭐️
  • Answer
  • February 5, 2021

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.

rakesh
sai_ram
ml2019
Terms & ConditionsCookie settingsAccessibility statement