4 Replies Latest reply on May 25, 2012 7:22 AM by Mike Balfour

    Beware NOW() and TODAY()

    Mike Balfour

      I just ran into an interesting quirk of Tableau.  The NOW() and TODAY() functions look like they use the time zone of whatever PC you run the report on.  With Tableau Desktop, that’s your time zone, and with Tableau Server, that would appear to be the server’s time zone.  In Oracle, Date and Timestamp columns by default don’t carry timezone information with them, so you’ll get back dates and times in whatever timezone they were created in.

       

      So…if you’re trying to create Tableau reports that show the duration between something and now, be aware that your duration might be incorrect if the two timezones don’t line up. 

       

      My workaround, at least with Oracle, is to define a couple of calculated fields.  In my case, the dates were stored in Central time, so I needed them to be calculated against Central time:

      ·         Now_In_Central_Time – RAWSQL_TIMESTAMP(“current_timestamp at time zone ‘America/Chicago’”)

      ·         Today_In_Central_Time – RAWSQL_DATE(“current_timestamp at time zone ‘America/Chicago’”)

       

      I can then use these fields instead of NOW() and TODAY() for my duration calculations.

       

      I couldn't find anything in the forums talking about this, so I figured I'd post something in case this has ever bitten anyone else.

        • 1. Re: Beware NOW() and TODAY()
          Dimitri.B

          Thank you for posting this, Mike.

          This is not so much a Tableau quirk (I think it does the right thing with NOW() and TODAY()), but more of a data awareness issue. In your case one needs to be aware that data's timezone is different to Tableau's, and cater for it.

          • 2. Re: Beware NOW() and TODAY()
            Shawn Wallwork

            7.0.4 has just been released. Here's a release not of interest to this discussion:

             

            The "Data Update Time" that can be inserted into titles and captions was not properly representing different locale settings. The time is now always expressed in the time zone of the computer that is opening the workbook. The format is determined by the workbook locale setting.

             

            --Shawn

            • 3. Re: Beware NOW() and TODAY()
              Mike Balfour

              I guess the big thing for me is that it would be helpful if NOW() and TODAY() optionally took a timezone.  I think it's a bit more than just a data awareness issue - I literally can't use these functions as-is to do any comparisons against time data from a database, since I can't determine what timezone (and DST/non-DST setting) they're in relative to my data.

               

              I've seen some other forum posts where people ran into similar issues, and they just hard-coded a timezone difference into their formula (something like "NOW() - 0.25" IIRC).  But my point is that you can't even use a trick like that if your DB timezone and your user's timezone have different DST settings (ex: UTC vs Central), since there will be a non-constant difference between the two timezones depending on the time of year.

               

              Another option that might work for some people, but that I can't do in my specific case, would be to use DB datatypes that preserve timezone information.  I think I've also read in forum posts that Tableau doesn't recognize or use that additional timezone information though, so this might not be a usable option yet.

              • 4. Re: Beware NOW() and TODAY()
                Mike Balfour

                That's great news!  I had noticed that problem too while investigating the NOW()/TODAY() problem, but that one was a bit more frivolous and cosmetic so I was just going to let it go.  Nice to know it's been fixed though.