Conversion of Standard Datetime format to Unix (Epoch) time

[Former Member]

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.

[Former Member]

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.