Skip to main content

Hi,



I can’t seem to figure out what’s wrong with my formula. I’m trying to convert a UNIX timestamp which is in a Single Line Text field, into a corresponding date field.



My UNIX is in a field called _eventTimestamp and value is 1663598125000 stored in a Single Line Text field.



My date field has the formula



DATEADD('1/1/1970',_eventTimestamp,'seconds')



and the formula results to 4/30/54687 2:16am



I can’t seem to figure out why that is. I checked the UNIX timestamp online and it shows me the correct converted date





It’s probably a blindspot but I can’t seem to figure out why I’m getting a weird value for the date in Airtable.



Any help would be much appreciated.



Thank you.





Update 1:


I even tried converting the UNIX timestamp to a numeric value thinking the DATEADD needs to add a number and so I tried the formula



DATEADD('1/1/1970',VALUE(_eventTimestamp),'seconds')



…but I still get this odd date 4/30/54687 2:16am





Update 2:



SOLVED



For anyone interested in the solution, I finally figured this out. It appears that my UNIX timestamp had three extra digits indicating the milliseconds since 1/1/1970 and I was using the DATEADD to add that number as seconds and so was getting 1000 times as much. I simply divided the UNIX timestamp by 1000 and then used the same formula which worked fine.

Hi @Burner,


Maybe you can explain a little more about what you are trying to accomplish.



The DATEADD formula is looking for a number in the second argument, not a date.



Are you trying to find the difference in your time stamp from Epoch?



1663598125000 seconds converted to years is 52752 years. If you are adding that to 1/1/1970 your formula is correct.

Hi @Burner,


Maybe you can explain a little more about what you are trying to accomplish.



The DATEADD formula is looking for a number in the second argument, not a date.



Are you trying to find the difference in your time stamp from Epoch?



1663598125000 seconds converted to years is 52752 years. If you are adding that to 1/1/1970 your formula is correct.


Thank you. I’m getting a unix timestamp from a Zoom integration that I’m using to create a record in Airtable. The Zoom payload is giving me a timestamp in this UNIX format. I’m inserting the raw UNIX timestamp as a text in one field and then trying to use a formula field to convert it to date.



I’m not sure I understand.



I thought the DATEADD is adding 1663598125000 seconds to 1/1/1970, which should give me the UNIX timestamp formatted as a date, no?

OK, i played around with it a little more.



Your time stamp is in milliseconds. to change your original formula to:



DATEADD('1/1/1970',_eventTimestamp,'ms')


An you should get the date: 9/19/2022 2:35pm

OK, i played around with it a little more.



Your time stamp is in milliseconds. to change your original formula to:



DATEADD('1/1/1970',_eventTimestamp,'ms')


An you should get the date: 9/19/2022 2:35pm


Yup, that’s it. I figured it out too. Thanks a ton. :grinning_face_with_big_eyes:

Yup, that’s it. I figured it out too. Thanks a ton. :grinning_face_with_big_eyes:


While you already have a working solution, I suggest you also consider this formula:



DATETIME_PARSE( {_eventTimestamp}, 'x')


'x' is the format specifier for the Unix millisecond timestamp. 'X' is the format specifier for the Unix timestamp in seconds.

While you already have a working solution, I suggest you also consider this formula:



DATETIME_PARSE( {_eventTimestamp}, 'x')


'x' is the format specifier for the Unix millisecond timestamp. 'X' is the format specifier for the Unix timestamp in seconds.


Thanks @kuovonne I initially tried that approach but got an error, which I now realize is because I was trying to parse UNIX with milliseconds. Thank you, this approach makes more sense as I’m trying to parse a date instead of adding to an epoch.

Reply


Terms & Conditions