0 Replies Latest reply: Nov 13, 2014 2:33 AM by Dominik von Francois RSS

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