How do I turn a date offset into a date

tim_ringle — Mon, 29 Jan 2018 17:52:00 GMT

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.

Re: How do I turn a date offset into a date

swuehl — Mon, 29 Jan 2018 19:08:23 GMT

LOAD

Timestamp(25569 + TimeCreated / 86400) as TimeCreated,

...

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

Re: How do I turn a date offset into a date

tim_ringle — Mon, 29 Jan 2018 19:16:40 GMT

Thanks,

That is exactly what i was hoping for.

topic How do I turn a date offset into a date in App Development

How do I turn a date offset into a date

Re: How do I turn a date offset into a date

Re: How do I turn a date offset into a date