4 Replies Latest reply on Aug 20, 2018 4:23 AM by Antonio Casado

    How does Tableau Server update the field Local Timezone Name in user_pref table

    Antonio Casado

      Hi all,

       

      I am looking into a way to show all dates updated dependingo on the local time of the logged user. I know that Tableau Server stores the Local Timezone of the user in the table user_pref, but I don't know how this field is updated by the server. I have tried changing the timezone in my laptop and login to Tableau server and this value is not updated, also I have changed the user configuration to set a new locale but it doesn't change either.

       

      Please, can someone help me to understand how this value is changed by Tableau Server?

       

      Many thanks in advance.

       

      Regards.

        • 1. Re: How does Tableau Server update the field Local Timezone Name in user_pref table
          patrick.byrne.0

          Hello Antonio!

           

          This is a very interesting question that I am currently working on getting some clarification on the exact operation of this within Tableau.

           

          In the meantime, to better understand the issue, what is the use case for the view? It seems as though you are trying to dynamically filter the view based on location when logging in? Is this information not already stored in a user table that could be referenced with row level security.

           

           

          Pixel Mixer: Row-Level Security On A Published Tableau Data Source

           

          Hope this helps!

           

          Cheers,

          Byrne, Patrick

          • 2. Re: How does Tableau Server update the field Local Timezone Name in user_pref table
            Monica Van Loon

            Are you dealing with UTC dates and trying to get them to be in local times?  We have had some issues with Salesforce which stores in UTC.  I.E if you create an opportunity at 6pm Calif time it shows up in the next day.  Salesforce Cloud adjusts for that but if data is brought into Tableau it does not and the opportunity shows up in the next dat.

            Have you seen this thread that is recent?

             

            Re: Data Adjusted to customer's Timezone when being displayed

             

            It had some good info.  Also we are looking into creating a dashboard extension to handle this it is a good use case I believe.

            From the thread

             

            Getting the timezone of the current user / client

            • If you're doing this at client level it would be a bit easier. In other words, if you can assume that a client all use the same timezone, you could set a UTC offset at the client level and store this in the database in a table or in the Tableau report as a parameter or calculated field
            • If you need to do this at user level i.e. the person opening the report, it gets a bit harder. The easiest way would be to give the client a dropdown parameter inside the Tableau report to control their timezone and create a bunch of DATEADD calculations in the report to adjust according to the offset (or pass it through to SQL and handle it in the database). The harder way, would be detecting their timezone - you'd need to write a webpage, embed the tableau server report in that webpage then use the Javascript API to pass the UTC offset through into the report / database to figure out the adjusted time.

            Converting the data to the target timezone

            • Once you have an offset for the timezone you can use the DATEADD function in Tableau to convert or the dateadd function in your database. Taking into account daylight savings is tricky, and you should do some reading on how to do that within SQL. In MSSQL you might be able to utilize something like TODATETIMEOFFSET

             

            TLDR: There is no native support for such a conversion inside Tableau, nor is there native support to detect the client timezone.

            • 3. Re: How does Tableau Server update the field Local Timezone Name in user_pref table
              Antonio Casado

              Hi Patrick,

              Thanks for your answer.

               

              Yes, I am using the Row-level security to get the Time Zone of the logged user. I have resolved this issue, but my remaining problem is that I don't know how Tableau Server is getting the Time Zone of every user, and I need to be sure that Tableau will continue using it in the same way in the future because I am implementing a Global Countinuous Integration Framework for my company.

               

              I will try to explain that solucion as clearer as possible:

               

              1. Tableau Server saves the user Time Zone in the user_pref table:
              2. I create a live connection to the PostgreSQL and apply a Row-Level Security for getting only the Time Zone of the current user. For doing this I create a calculated field with the following easy formula: [Name] = USERNAME() and then, apply the filter to get only True values (obviously only 1 row will be filtered)
              3. Then, I connect my workbook to my regular datasource that contains a DateTime column stored as (UK Time +0):
              4. I blend both datasources (TabUserDataset = Dataset that contains only the data related to the logged user) using a dummy calculated field that returns "1".

               

              From that point, you can continue in many different ways, but recomend the following two soluctions:

               

              1. Create a calculated field with a Case When formula and apply the Dateadd formula to every different Time Zone:
              2. Connect your Tableau Desktop to an R Server and create an R Script that makes all that work for you (This is my favourite):

               

              And your final result is:

              • Opening the Workbook from Spain:

              • And opening the same workbook from Australia:

               

              Now I need that someone in Tableau Team give me more information about how Tableau Server is tracking the user Time Zone and what are they plans about this, because I don't want to deliver a framework based in something that Tableau might change in the future.

               

              I hope this solution be helpful for all

               

              Many thanks.

              1 of 1 people found this helpful
              • 4. Re: How does Tableau Server update the field Local Timezone Name in user_pref table
                Antonio Casado

                Please, see my response to Patrick. I hope my solution can be useful for you.

                 

                Many thanks.