I'm pulling some data from an API into Retool using a REST API Resource, and then using a Query JSON with SQL resource to search it. One of the fields is a unix timecode eg. 1620531567 and none of the normal SQL functions to turn that into a normal date seem to be working. I know alaSQL is used for this, I just can't figure it out. I'm a bit of n00b, be gentle 
Hi @joshmohrer
Retool comes pre-loaded with moment.js(Moment.js | Docs). So you can use moment to convert unix timestamps to a standard date MM/DD/YYYY or standard date/time format MM/DD/YYYY HH:mm.
Here's a sample with your timestamp 1620531567:
This is just using the text component. You can also edit the column inside a table by adding moment as shown above.
Hope it helps!
3 Likes
Brilliant. that works. Thank you so much!
1 Like
How do I change the Unix timestamp in a table.
One of the value is a Unix Time stamp and I need to change it.
Hey @dh00mk! Would you like to change the E way it displays in the table only or change it in the data source itself? If just in the table, you can use a column mapper in the column settings! And in this mapper, you can use momentJS. Maybe something like {{moment(self).format.(‘mm/dd/yy’)}}, where that string depends on what shape you’d like to display your date in
Docs here!
Hey thanks for the reply and really sorry for the delay in response.
Can you point me in the right direction here please:
Here's the kind of data I have
And this is what I am doing to convert it:
No worries at all!
Ah, gotcha. It looks like your data is in nanoseconds, so instead of something like:
{{ moment(self).format('lll')}}
You can try something like:
{{ moment(self/1000000).format('lll')}}
Let me know how this works for you!
This has been very helpful for working with tables. What if I have a chart that I am trying to change to a "normal" date format? My x axis values are defined as {{formatDataAsObject(getCharges.data.data)['created']}}. If I try to add the moment change here it errors out and if I just format the x axis as a date, the values are all from the 1970s.
Thank you!
So glad to hear it!
Basically, you’d like to loop through your getCharges data and change all created dates using moment?
If so, maybe something like this:
{{formatDataAsArray(getCharges.data.data).created.map(d => moment(d) }}
I do know that moment sometimes gets weird when inside a map function inside double Curleys, so we may need to try different iterations of this using JavaScript Date or maybe a separate JavaScript transformer.
Let me know!
Hi,
My timestamps are in the format of 1688569649.0890446, so the previous solutions result in an "Invalid Date" error from Moment.js
I've tried {{ moment(Math.floor(self)).format('lll')}} but Moment still returns "Invalid date". For the dashboard I am building in Retool I don't need nanosecond precision, so truncating the UNIX timestamp at the decimal place will work for me.
Hey @samdcbu! Have you trried using moment.unix?
For using Xero's dates, divide by 1000 then use moment.unix()