Crystal Reports – Converting UNIX Time Stamp

Jan 19

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

none

Comments

Theresa

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
Ivan

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

April 10th, 2007 at 8:08 AM

Love it!
Thanks-
Laurie
Raymond

May 30th, 2007 at 9:30 AM

Thanks for the tip, works great!
Dan Santoni

July 24th, 2007 at 12:19 PM

You’re a genius, thanks