Getting strange unix to date conversion

[Burner]

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.

[Vivid-Squid]

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.

[Burner]

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?

[Vivid-Squid · Accepted solution]

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

[Burner]

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

[kuovonne]

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.

[Burner]

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.