1 Reply Latest reply: Apr 7, 2017 6:10 AM by Wessel Hanekom RSS

    Help: Converting Unix Times to DD/MM/YYYY

    Lorcan Chinnock

      Hello, I have a data field which is in Unix Time format (eg. 1451610061) and I want to create a new date afield which has regular date format. I am very new to qlik sense and I have no idea how to do it. I will paste in the LOAD for the table of the data field in question. The datafield is called dateline:

      LOAD ticketid,

          dateline,

      SQL SELECT ticketid,

          dateline,

        • Re: Help: Converting Unix Times to DD/MM/YYYY
          Wessel Hanekom

          Good day Lorcan,

           

          This is an old post but I had the same problem and found little help from the community that I could understand as a newbee to Qlik.

           

          I actually did it with SQL not with Qlik but I will post the Qlik solution once I understand it, I have included both the Qlik load and SQL select statements so that you can understand where in your load script to use the code:

           

          LOAD dateline,

          SQL SELECT dateline = DATEADD(S, CONVERT(int,LEFT(dateline, 10)), '1970-01-01')

          FROM ...;

           

          UPDATE

          I have found that the above SQL SELECT calculates data to UCT. I live in a UCT +2 Time Zone with no Daylight Savings. I needed to update my script to the below to add two hours to my timestamp:

           

          SQL SELECT dateline = DATEADD(HOUR, 2 , DATEADD(S, CONVERT(int,LEFT(dateline, 10)), '1970-01-01')),