2 Replies Latest reply: Jan 29, 2018 2:16 PM by Timothy Ringle RSS

    How do I turn a date offset into a date

    Timothy Ringle

      I have a database (sterling Gentran for Windows) that stores a date as an interger representing the number of seconds from midnight of 1/1/1970 GMT that an activity happens.

       

      For example the datebase shows a created time of 1497224413

      Which represents 06/11/2017 17:40:13 CST

       

      What is the best way to get this data as a datetime in Qlik? I can use the follwoing SQL script to convert it to CST.

      ,dateadd(second,(TimeCreated + (-6*3600),'1/1/1970') as TimeCreated

       

       

      The issue occurs when I store that and want to retrieve in local time. Depending on the time of year the hour offset changes from -6 to -5. Given that Qlik stores timestamps as integers as well and handles the time zone conversion, is there an easy way to covert the Gentran offset to the Qlik Integer and store it that way?

       

       

      Or is there an easier way to handle this?

       

      Thanks in advance.