Epoch SQL Field conversion to date and time using Expression

Thu, 13 Nov 2014 07:33:10 GMT

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).

topic Epoch SQL Field conversion to date and time using Expression in QlikView

Epoch SQL Field conversion to date and time using Expression