Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Getting strange unix to date conversion

Topic Labels: Dates & Timezones
Solved
Jump to Solution
5702 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Burner
7 - App Architect
7 - App Architect

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

image

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.

1 Solution

Accepted Solutions
Vivid-Squid
11 - Venus
11 - Venus

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

See Solution in Thread

6 Replies 6

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?

Vivid-Squid
11 - Venus
11 - Venus

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:

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.