1

Hello!

As the title suggests I need to convert a column of unix timestamps to a human readable date using the query results data source. So far I have not found any solutions to this.I’m using query results because the data comes from connecting a google sheet to redash and as far as I know, there isn’t any other way to modify the resulting table.

Is there a way to do this with query results or should I try something entirely different?

2

QRDS uses SQLite, which includes date time functions for exactly this purpose :smiley:

3

Yep, you are absolutely right! I should’ve figured that out myself, so thank you for pointing me to the right place. However, for some reason using date() doesn’t convert the timestamp to date, whereas datetime() converts the timestamp correctly. According to the article there should be no difference though.

Examples:
Using datetime(1550826041, ‘unixepoch’) >> 2019-02-22 09:00:41
but using date(1550826041, ‘unixepoch’) >> 1550793600000

The resulting value from using date() is in milliseconds I’m guessing. I checked the date in an online converter and deleted the last 3 zeroes and got: February 22, 2019 12:00:00 AM; so the date itself is correct but the time, which I don’t even need, seems to be modified.

Have you any insights why this might be happening? My initial problem is essentially solved, I can just keep using the datetime() but I’d prefer just the date.

4

What version of Redash are you using? I just ran the following query against a QRDS in version 8.0.0+b32245:

select date(1550826041, 'unixepoch')

and the result was:

2019-02-22 00:00

5

Yep, I’m using the same version. Just checked and entering the timestamp manually into the function works the same way you’ve pointed out. The problem occurs when passing the timestamp to the function through the column name.

6

What happens if you CAST it first?