Help

Re: Format Unix timestamp as human readable date

2427 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Vernon_Fowler
7 - App Architect
7 - App Architect

Using Buffer (through Zapier) I get a Unix timestamp for when each post is scheduled.
An example Unix timestamp is 1497503280.
The expected human readable equivalent is 15/6/2017 3:08pm (for Australian Eastern Standard Time which is GMT +10).

Using a formula based on Excel convert (via https://www.epochconverter.com/), the nearest I can get to seeing the output in a human readable form is by:

DATETIME_PARSE( ( ( {BufferUnixTimeStamp} +36000 ) / 86400 ) +25569 )

This doesn’t work as all it produces is the start of the epoch (1/1/1970 12:00am) no matter what the input value is for {BufferUnixTimeStamp}.

Hope someone can help with converting Unix timestamp to a human readable date time.

5 Replies 5
Christoff
7 - App Architect
7 - App Architect

DATETIME_PARSE() requires an input that is formatted like a date, e.g. “06/05/17” or “6 May 2017”.

You can use the DATEADD() function to convert the Unix timestamp.

For Australian Eastern Standard Time, use: DATEADD(“1/1/1970”,({BufferUnixTimeStamp}+36000),“s”).

Bingo! Thank you @Christoff That’s perfect! Yihaa!

Alternatively, you can use

DATETIME_PARSE({BufferUnixTimeStamp},'X','Australia/Sydney')

‘X’ says the input string is a 10-digit Unix timestamp; for the 13-digit Unix millisecond timestamp, use ‘x’. Unfortunately, you’re limited to ‘M/D/YYYY h:mm a’ as the output format…

Thanks @W_Vann_Hall That’s much neater! And we can format differently via the Formatting tab.

e7e7b051e8e32b44040568656d8ba21a8ffd60b5.png

Voila! :thumbs_up:

Heh… That’ll teach me to take the Airtable Formula Field Reference at its word. :winking_face: