Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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.

Former Member
Not what you were looking for? View more on this topic or Ask a question