Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

tim_ringle
New Contributor II

How do I turn a date offset into a date

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.

1 Solution

Accepted Solutions
MVP
MVP

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



LOAD

     Timestamp(25569 + TimeCreated / 86400)  as TimeCreated,

     ...



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

2 Replies
MVP
MVP

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



LOAD

     Timestamp(25569 + TimeCreated / 86400)  as TimeCreated,

     ...



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

tim_ringle
New Contributor II

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

Thanks,

That is exactly what i was hoping for.

Community Browser