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