Crystal Reports & Converting a UNIX Time Stamp
I had a heck of a time finding out how to convert a UNIX time stamp in Crystal Reports. My company has a ecommerce package that stores all sales date in a UNIX time stamp format. Which means it stores the time and date by storing the number of seconds since 1/1/1970. So here is the formula I came up with…
dateadd (“s”,{order.date}-18000, #1/1/1970#)
dateadd function does the trick, the “s” is for seconds, order.date is the table that contains the unix time stamp, -18000 is -5 hours from UTC in seconds because im in the Eastern Time Zone and 1/1/1970 is the base date for dateadd to add the other argument to.
Hope This Helps!
Chris Edwards
February 28th, 2007 at 12:55 pm
Hi,
Thanks for info. I have one quick question. Why use the Eastern Time Zone? I am in MST so I need to adjust the “-18000″ by 1 hr to “-14400″ to get the right time, is there any other way to calculate the date string from the unix timestamp without making this adjustment? The reason I ask is because our client is in a different time zone. I am thinking that it will have somekind of side effect with the CR report object. I have already experienced some time calculation errors with regard to daylight savings time already.
Thanks,
T
March 15th, 2007 at 5:17 pm
Thanks for the information. My company makes an ecommerce package that stores all dates in UNIX time stamp format. A customer was asking us about how to report on dates from Crysal.
April 10th, 2007 at 8:08 am
Love it!
Thanks-
Laurie
May 30th, 2007 at 9:30 am
Thanks for the tip, works great!
July 24th, 2007 at 12:19 pm
You’re a genius, thanks