Re:dash overriding explicit date formatting in query results

[Michele Coleman]

When I run the following query against a Redshift instance:

SELECT 
    MAX(start_time) as val1,
    TO_CHAR(MAX(start_time),'MM-DD-YYYY') as val2,
    TO_CHAR(MAX(start_time),'YYYY-MM-DD') as val3,
    TO_CHAR(MAX(start_time),'Mon DD, YYYY') as val4    

FROM some_table

I get the following results:

Val1          Val2    Val3           Val4

10/03/15 00:00  10/03/15 Mar 10, 2015

The 'MM-DD-YYYY' is blank, and the 'YYYY-MM-DD' result has been overridden (the date formatting has been ignored or reversed).

The expected results (which I get when I run this query in a different SQL client) are:

Val1          Val2         Val3           Val4

10/03/15 00:00   03-10-2015   2015-03-10     Mar 10, 2015

Since I work for an American company, I know with certainty that the default DD/MM/YY formatting will confuse my coworkers (the consumers of my reporting dashboard). They would understand any of the other 3 formats. The 'YYYY-MM-DD' would be expected to sort correctly in an output table widget – but re:dash is converting it into DD/MM/YY format.

Actually I'm getting really inconsistent results with the one value being blank. Sometimes I change the format string to one that has been known to work and it still comes out blank. Then I make a minor change to the formatting string and it works (result not blank). Then I use command-Z to undo the change I just made so that it's back to an earlier version that failed and then when I run the query again it works! Honestly it's driving me crazy. So I cannot reliably reproduce the behavior of a certain formatting string leading to an empty result. However it is definitely the case that re:dash is overriding the 'YYYY-MM-DD' format. 

 

[Arik Fraimovich]

Hi,

Sorry for the late response: for some reason Google Groups flagged both of your messages as spam. I cleared them and marked all your following messages to be "safe".

As for the formatting issue: whenever re:dash detects a date, it applies its own formatting overriding whatever formatting you set in the query. It will be harder to make it not change existing formatting, but I will add an option to change the default formatting, so you can use American formatting (MM/DD). 

Re. val2 column not showing up: the only known issue that might cause a column to be blank is when the column name contains special characters or unicode characters (you can see an example here: http://demo.redash.io/queries/184/source). Is it possible your column name might had Unicode characters?

-- 

Arik Fraimovich

--
You received this message because you are subscribed to the Google Groups "re:dash users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to redash-users...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Arik Fraimovich]

Hi,

I've fixed re:dash to show dates and times in user's format. To make sure this fixes your issue, please open the following query: http://demo.redash.io/queries/188

If you see the dates and times in correct format there, it means the issue is fixed. You will need to update your re:dash instance to get this fix too, you can see details on how to upgrade here: redash.io/deployment/upgrade.html

And of course, feel free to reach out if you need further assistance.

Arik.

-- 

Arik Fraimovich