I am using self-hosted re:dash version 3.0.0 on ubuntu.
I created a query which returns “start_date” column as a date type (YYYY/MM/DD) in descending order.
And I need to use the result in another query, so I used that result as Query result data source, and execute another query.
What happened to me was that the “start_date” column’s value was somehow transformed to timestamp type (looks like YYYY/MM/DD HH:MM), and thus we can’t properly process data in some cases.
Why did that happen?
And, I would love to know any workaround if somebody knows that.
I have a query with a timestamp column.
In this query I’m showing the timestamp value (1569801600000) as date using cast(start_date as date) inside the SELECT statement and it’s showed as 30/09/19.
I need to combine data from different datasources so I’m using Query Results for this.
When I do "SELECT * FROM query_idnum’ it shows again the date as 1569801600000.
Hope it’s clear.
To be clear: the way Redash shows a timestamp doesn’t affect query execution. It’s no different than changing the font-size or color.
The real issue if you need to JOIN on these timestamps is that records from the same day / week / month / quarter must match exactly. In which case changing the format won’t help at all.
You have two options:
Cast to VARCHAR instead of DATE and freeze the format that way.
Continue casting to DATE but use DATE_TRUNC to normalize the output.
I’m not joining on the timestamps. I have only a different visualization on the two queries: date after casting on the first, timestamp again on the second.