How do I turn a date offset into a date

[tim_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.

[swuehl · Accepted solution]

LOAD

     Timestamp(25569 + TimeCreated / 86400)  as TimeCreated,

     ...

Use Qlik time functions like ConvertToLocalTime() for your timezone conversion.

[tim_ringle]

Thanks,

That is exactly what i was hoping for.