Skip to Content
avatar image
Former Member

Conversion of Standard Datetime format to Unix (Epoch) time

Hi,

I am having trouble getting efficient record selection for time ranges on DB2 tables that use the UNIX epoch time stamp.

E.g., for a 1 minute time range we would currently use SQL such as:

SELECT * FROM DATABASE.TABLE WHERE ((TIMESTAMP('1970-01-01-00.00.00.000000') + (("EPOCH_TIME" + 3600000)/1000) SECONDS)>={ts '2008-09-04 14:29:00'} AND (TIMESTAMP('1970-01-01-00.00.00.000000') + (("EPOCH_TIME" + 3600000)/1000) SECONDS)<{ts '2008-09-04 14:30:01'})

This is the equivalent of:

select * from DATABASE.TABLE where EPOCH_TIME between 1220534940000 and 1220535000000.

Now clearly the problem is the top SQL doing a calculation for every record (up to 5,000,000 records) to select the data. It takes approximately 5 minutes to return the data. The bottom SQL takes LESS THAN A SECOND.

Please, please, please could someone help us with this? We need to be able to enter a datetime variable, and have the SQL sent to the DB2 table in the format of the lower SQL above.

We are using a BO XI Enterprise server to do this.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Sep 05, 2008 at 10:35 AM

    OK Everyone.

    This is how to do it:

    Use the following (for Crystal Reports XI)

    DateDiff (intervalType, startDateTime, endDateTime)

    as:

    Datediff ("s",(datetime(date(1970,01,01)),time(00,00,00)),currentdatetime)

    Looks a bit too simple, but does work. Look out for differences between the results due to daylight saving.

    Add comment
    10|10000 characters needed characters exceeded