Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Handling non-US format dates
Posted July 11th, 2008 by Richard Leeke
Importing text files with dates in the "proper" ;-) order (dd/mm/yyyy) can be a real pain.
It would be nice if Tableau tried to guess the date format - perhaps based on locale or country settings?
I generally either have to re-format with a perl script first or treat the date as a string and create a messy calculated field using date functions.
Subscribe to the comments on "Handling non-US format dates"
Comments
Thanks for the suggestion Richard. We are continuously trying to improve Tableau so that it not only follows best practices in data visualization but also identifies your data as accurately as possible. I'll make sure your suggestion gets passed on to our development team. How are your dates generally formatted?
I analyse data from all sorts of sources, with dates in all sorts of formats (over which I have no control). The particular example that inspired me to make the suggestion was a date/timestamp in this format:
30/06/2008 00:00:44.662466
There were actually two issues with interpreting this. Firstly the non-US date format (dd/mm/yyyy) and also the fractional time - in this case down to the microsecond. The fractional time is a less common need - and probably a less reasonable ask - though that would be nice too.
Some common date formats I get are:
dd/mm/yyyy - 30/06/2008
dd/mm/yy - 30/06/08
dd-mm-yyyy - 30-06-2008
dd-mm-yy - 30-06-08
dd-mmm-yyyy - 30-Jun-2008
dd-mmm-yy - 30-Jun-08
yyyymmdd - 20080630
The first 6 are all guessed correctly be Excel - not surprisingly the 7th one is just too ambiguous. Try it - create a CSV file like this:
date1,date2,date3,date4,date5,date6,date7
30/06/2008,30/06/08,30-06-2008,30-06-08,30-Jun-2008,30-Jun-08,20080630
Open it in Excel and it opens it like this:
date1 date2 date3 date4 date5 date6 date7
30/06/2008 30/06/2008 30/06/2008 30/06/2008 30-Jun-08 30-Jun-08 20080630
The first 6 are correctly picked as dates - with a couple of different format options chosen. Note that I've made the dates unambiguous by chosing the 30th - but Excel also gets it right if the day of the month is <= 12.
I haven't experimented - but I presume the interpretation of the order is based on regional settings.
Thanks Richard, that's really helpful information and as I mentioned, I'm passing it on to our development team. Are there others out there using date formats that Richard hasn't covered?
Sorry - I've just discovered that I've been maligning Tableau here - it handles dates much better than I thought.
Tableau actually correctly interprets formats 1 to 6 of my 7 example dates as long as there aren't fractional seconds. I was sure I'd tried that - but evidently I must have done something wrong. As long as the timestamp only goes down to whole numbers of seconds it works fine.
I was also sure that I had tried using the DATETIME() function on the relevant substring (chopping off the fractional seconds), and that that had not worked either, so I had ended up parsing out day, month, year, hour, minute and second individually in order to construct a datatime. But I just tried again on my sample data and it worked fine with this:
DATETIME(LEFT(date_field, 19))
Nonetheless - coping with sub-second time accuracy would also be really useful - even to the millisecond - microsecond accuracy is quite an unusual need.
Using Excel for comparison again, it doesn't quite cope with the microseconds in the original example I quoted - but it does recognise it as a time with fractional seconds and rounds to the millisecond correctly.
Sorry about the confusion.
The fractional second syntax Richard showed is a common way to format Oracle TIMESTAMP fields