4 Replies Latest reply: Dec 15, 2014 12:01 PM by Thilo Beckmann RSS

    How can I convert a timestamp into another timezone

    Thilo Beckmann

      For performance and storage reasons we store timestamp values in two separate columns as written here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/23/the-master-time-table

       

      The data we need to load is stored inside a QVD file (incremental load into those QVD files, and afterwards load all data with a query from those files). The QVD contain two column "Date" and "Time" with a split timestamp information. The data contains UTC timestamps in e. g. the following style

      Time (in UTC)Date (in UTC)
      11:3512/12/2014
      13:5612/12/2014
      and so on...

       

      Inow want to shift data to match a specific timezone but keep the QVD files unchanged (e. g. shift to GMT-10:00 to keep it easy).

      Because the time and date information is splitted I need to combine them again.

           Timestamp#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm') AS "Parsed Timestamp",

      Should do the trick. And the datamodell shows it works: "12/12/2014 11:35" is shown in the table preview.

       

      The load script:

      LOAD *,

          Timestamp#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm') AS "Parsed Timestamp",

          Date#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm') AS "Parsed Date",

          ConvertToLocalTime(Timestamp#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm'), 'UTC-10:00') AS "Shifted Timestamp",

          ConvertToLocalTime(Date#("Date" & ' ' & "Time", 'YYYY/MM/DD hh:mm'), 'UTC-10:00') AS "Shifted Date",

         

          ConvertToLocalTime('2007-11-10 23:59:00','UTC-10:00') AS "example 1 from documentation",

          ConvertToLocalTime(UTC(),'UTC-10:00') AS "example 2 from documentation"

         

          FROM '$(QvdConnection)/someQvdFile.qvd' (qvd)

          WHERE "Application ID"='$(ApplicationId)';


      Table preview

      The output in the datamodell table preview will look like this

      TimeDateParsed TimestampParsed DateShifted TimestampShifted Dateexample 1 from documentationexample 2 from documentation
      11:3512/12/201412/12/2014 11:3512/12/2014 11:35--11/10/2007 1:59:00 PM12/12/2014 2:46:13 AM
      13:5612/12/201412/12/2014 13:5612/12/2014 13:56--11/10/2007 1:59:00 PM12/12/2014 2:46:13 AM
      ...

       

      The Timestamp# and Date# methods return something, and it looks correct (although the AM information is not there).

      The ConvertToLocalTime method does work with UTC-10:00, but with GMT-10:00 you get the same results (the examples on in the documentation work).

       

      Why does the ConverToLocalTime method ignore my timestamp or date input?

      What do I have to change to get this working?

       

      Thanks a lot for helping me out :-)

       

      Thilo

        • Re: How can I convert a timestamp into another timezone
          Marcus Sommer

          I assume the reason is that ConvertToLocalTime couldn't interpret the input properly. Maybe you creates the same format like your manually example or you wrapped your timestamp#() into a timestamp().

           

          - Marcus

            • Re: How can I convert a timestamp into another timezone
              Thilo Beckmann

              How can I see if the ConvertToLocalTime method could not parse the input? Is there any logfile I can have a look at? or something I could see in debug mode? For me it is like a blackbox with to less information on error behavior.

               

              This extends to timestamp#, I assume that if the table preview show a value there the method was successful. And the documentation of ConvertToLocalTime expects a "timestamp" as first argument, so this should be fine, isn't it?

               

              But I'll try the timestamp(timestamp#( ...) ) approach, although it looks like wasting resources and bad coding style :-)

                • Re: How can I convert a timestamp into another timezone
                  Marcus Sommer

                  I suggest you tried it first in a qv textbox with one or two original values manually and if this worked it should work in script, too.

                   

                  - Marcus

                    • Re: How can I convert a timestamp into another timezone
                      Thilo Beckmann

                      I tackled the problem. It was a wrong date format...

                      When using the correct date format, everything works

                       

                      Now it only depends on your flavor, if you want to use a date a timestamp, wrap the parsing call into a formattings call or use even time formating

                       

                      LOAD *

                      ,ConvertToLocalTime(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'UTC-10:00') AS "Shifted Timestamp"

                      ,ConvertToLocalTime(Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'UTC-10:00') AS "Shifted Date"

                      ,ConvertToLocalTime(Timestamp(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm')), 'UTC-10:00') AS "Shifted Timestamp from Timestamp"

                      ,ConvertToLocalTime(Date    (Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'YYYY-MM-DD hh:mm:ss'), 'UTC-10:00') AS "Shifted Date from Date"

                      ,ConvertToLocalTime(Date    (Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm')), 'UTC-10:00') AS "Shifted Date from def Date"

                      ,ConvertToLocalTime(Time(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm')), 'UTC-10:00') AS "Shifted Time from Timestamp"

                      ,ConvertToLocalTime(Time(Date#    ("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'YYYY-MM-DD hh:mm'), 'UTC-10:00') AS "Shifted Time from Date"

                        

                          FROM '$(QvdConnection)/someQvdFile.qvd' (qvd)

                          WHERE "Application ID"='$(ApplicationId)';

                       

                      They all return the same value, that the reason why I will use ,ConvertToLocalTime(Timestamp#("Date" & ' ' & "Time", '$(DateFormat) hh:mm'), 'UTC-10:00') AS "Shifted Timestamp" because it is simple and does the trick.