epoch time conversion

[jtaker]

Good day,
I need to know how to convert epoch time to a normal readable string. I've tried using the following formula but all my dates are consistently off by 5 hours.

here is the formula I used:
dateadd("s",{HPD_HELP_DESK.REPORTED_DATE},date(1970,1,1))

Any help would be greatly appreciated.

Thanks
Jason

 

[mbarron]

Your formula is correct. Does consistently = always off by 5 hours? If it is, just add another dateadd.

dateadd("h",-5,dateadd("s",{HPD_HELP_DESK.REPORTED_DATE},date(1970,1,1)))


Mike

 

[jtaker]

Thanks mbarron.
I modified the formula to try yours which sounds logical (although I'd still like to know why this basic formula doesn't do what it says it does!) but if you can believe it, it actually subtracts 6 hours and not 5. This is a little insane!!

Any other thoughts though would be more than welcome!!

 

[mbarron]

What does this formula give you? It should give you the current date and time.

numbervar epoch:=datediff("s",datetime(1970,1,1,0,0,0),currentdatetime) ;
dateadd("s",epoch,date(1970,1,1))



Mike

 

[MJRBIM]

This issue is Time Zone related, epoch time shows GMT - and Eastern Time is 5 hours behind.

Therefore subtract the TimeZone Difference (5 hours x 3600 seconds per hour) from the epoch time field.

dateadd("s",({HPD_HELP_DESK.LAST_RESOLVED_DATE}- 18000),datetime(1970,1,1,0,0,0))

If your database server is in a different timezone then you would have to adjust the subtracted amount for whatever timezone you are in relative to GMT.

You may also experice a 1 hour slide based on Daylight Savings.

 

[jtaker]

Thanks very much...I had just implemented that same formula and it worked...forgot all about the GMT issue!!!

I'm not sure about the daylight savings time though...if the time is recorded based on the OS time, should this not be address?

I'm running some tests now to see the outcome, I'm hoping this will not be an issue.

thanks again