Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Epoch SQL Field conversion to date and time using Expression

Hi again,

I actually wanted to ask this as a question , but while I was typing, I solved it somehow and would like to share it with the community as this works for me, so maybe it works for someone else too. So I have a SQL field which uses epoch time, let's call it creationdate . Now I would like to convert it to a date time format. The data looks like this as example : 1394785880 . And it should look like this : 2014/03/14 8:31:20 AM.

here is the expression I used :

          Timestamp(Timestamp('1970-01-01 00:00:00.000') + creationdate/60/60/24)    

initially, the expression was Timestamp(Timestamp('1970-01-01 00:00:00.000') + creationdate/1000/60/60/24)

but that didn't work and gave me all dates as 1970-01-01 BUT gave me a correct time. Well time, yes , but correct time no. I had to then change the final expression to

          Timestamp(Timestamp('1970-01-01 00:00:00.000') + (creationdate + 7200)/60/60/24)

since the time was out by 2 hours (GMT+2).