1 Reply Latest reply: Sep 16, 2016 4:49 PM by Toni Kautto RSS

    Converting time zones: convert_tz query?

      Hi everyone! I'm working on an initial data extraction of historical data and was wondering whether I can use the ConvertToLocalTime in place of the SQL CONVERT_TZ query? The extraction includes data from 2012 and my understanding of the ConvertToLocalTime query is that it converts dates based on the computer's time setting and thus would disregard daylight savings conversions for past data. Have I understood it correctly? If so, what are my alternatives to correctly convert my current and past dates?


      This is the SQL query I normally use to convert my date columns (here, the column created_at):

                DATE(CONVERT_TZ(created_at, 'UTC', 'America/Sao_Paulo'))


      I could apply the convert_tz sql query in the Load Sql Select but I want my initial load to be a pure extraction of the mySQL data source, I believe this optimizes the extraction?


      Thank you!!



        • Re: Converting time zones: convert_tz query?
          Toni Kautto

          In QlikView a timestamp is just a timestamp. It does not have timezone information. If you have timestamps from various time zone you need to make sure to flag these in QlikView, for example by adding a timezone field. The other option is that you convert all timestamps to a common timezone during load.

           

          Processing the timestamp in the SQL query or the LOAD statement will likely not make much difference performance wise. Just a matter of preference.