Jan 19

Crystal Reports - Converting UNIX Time Stamp

Category: General B.S., Linux

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

5 Comments so far

  1. Theresa February 28th, 2007 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

  2. Ivan March 15th, 2007 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.

  3. Laurie April 10th, 2007 8:08 am

    Love it!
    Thanks-
    Laurie

  4. Raymond May 30th, 2007 9:30 am

    Thanks for the tip, works great!

  5. Dan Santoni July 24th, 2007 12:19 pm

    You’re a genius, thanks

Leave a comment

You must be logged in to post a comment.