Skip navigation

Prep: Do not use local timezone when converting to Date data type from Date-only data

score 2
You have not voted. Active

This feature request is that when a given text field has date information for Tableau to only consider that information when converting the data type to a date (and ignore all other information including the local timezone on the  machine running Prep) so that way a date is treated as a date no matter what year the date occurs in.


For example John Snow went to the local council in London on 7 September 1854 to ask them to disable the water pump on Broad Street in London, whose water he believed was the source of the cholera outbreak that killed 616 people. This is one of the most famous examples of data use in public health and had impacts on public sanitation & disease theory that most likely helped the ancestors of everyone reading this feature request to survive. (It’s that big a deal.) Steven Johnson’s The Ghost Map is a fantastic book on the subject.  However in Tableau Prep (as of at least 2019.4) if we start with “1854-09-07” as text or “18540907” as a number and use date datatype conversion or MAKEDATE() or DATEPARSE() then Tableau Prep displays 6 September 1854 if your timezone is Eastern Time (or some set of other timezones). In other words Tableau Prep has managed to go back in time and move this significant event back a day.


Here’s a screenshot of Tableau Prep. The “Raw Date as String” field shows the original value, for example 1854-09-07. The “Date Datatype from Date as String Field” is showing 09/06/1854, one day before.


Screen Shot 2019-12-19 at 9.04.35 PM.png


If I’m loading data with this date into Tableau Prep, or the Battle of Hastings on 14 October 1066, or the signing of the Declaration of Independence on 4 July 1776, etc. then I expect to see those those dates in Prep, not the day before. (Prep is awesome, but it's not a time-travel machine!).


We can get a better clue as to what is going on by looking at the “DateTime Datatype from String Field”, there for all dates from 1883-11-18 and earlier Tableau Prep has subtracted 2 seconds from the date, moving it to 11:59:58 on the prior day. So even though our data started with just the date Tableau Prep is displaying the date with an implicit time. At some point in the 2019 releases Tableau upgraded the Java library used in Tableau Prep for date conversion. When I reported this to Tableau support in case #04993751 here's what I was told:


With the new versions of Java, there was a change on how the UTC is calculated and your specific local. So now the time-zone conversion does not work by just backward interval of seconds, it is calculated as what local time was being observed in a specific region. Please see the following article on Stackoverflow about time offsets (with examples):


So from that point onwards Tableau Prep is using the local timezone on the machine in conversion to date datatypes for display and the net result is that leap seconds are included and this behavior is (and I quote) "by design". The goal of this feature request is to change the “by design” behavior to something that matches user expectations. In particular my expectation is that if I’m asking Prep to convert dates from text or numbers with just date parts (year, month, day) that Tableau is only going to consider the dates and not times or timezones. In this situation I haven’t given Prep a time, nor have I told Prep what timezone that original date was in, and the given date might even be from a time *before timezones ever existed* so Tableau Prep should not be considering that (or my machine's timezone) in displaying the dates.


The current behavior is even odder in that this change is only for display purposes:


1) In the below screenshot note the String from DateTime Datatype results, that is using STR([Datetime Datatype form Date as String Field]) and showing the correct datetime, e.g. 1854-09-07 00:00:00. So the data starts out with the correct date, the data type conversion displays the (wrong) date, and when we do a string conversion we get the accurate date.


Screen Shot 2019-12-19 at 9.06.41 PM.png


2) And when we do an output from Prep (whether CSV or Hyper extract) all of the dates are output as the correct value:


Screen Shot 2019-12-19 at 9.07.32 PM.png


Also since most users of Tableau Prep are also using Tableau Desktop there’s an additional source of confusion since Tableau Desktop treats dates like 1854-09-07 as-is throughout both display and calculations.


The core goal of a data preparation tool is to help us work with the data as-is so we can get to a usable data set for further analytics. However in this case Tableau Prep is not showing us the data as it exists and is therefore taking the locus of control away from us users. This creates unnecessary confusion for users and I’d like Tableau Prep to ignore any timezone offsets when the data just has dates in it.


The v2019.4 packaged workflow used to create the above screenshots is attached.


Vote history