finding time difference in DB2
How can I write a query in DB2 for following thing:
The difference between current timestamp and a timestamp field in dB should be >=4 hours AND <= 24 hours
Someone suggested this but it's not working.
select * from tableName where date <= DATEADD([hour], -4, CURRENT_TIME) and date date >= DATEADD([hour], -24, CURRENT_TIME)
But it's not working. It's giving following error.
SQL0104N An unexpected token "[hour]" was found following "ortdate <= DATEADD(". Expected tokens may include: "<func_arg_list>". SQLSTATE=42601
select * from table t where t.tscolumn between current timestamp - 24 hours and current timestamp - 4 hours
Use just Hour instead of [hour]
select * from tableName where date <= DATEADD(Hour, -4, CURRENT_TIME) and date date >= DATEADD(Hour, -24, CURRENT_TIME)
DB2 doesn't like square brackets around name - that is a MS SQL Server mannerism.
The only reference to DATEADD() in the DB2 9.7 Info Centre (oh, beg its pardon: Center - one day, American's will learn to spell correctly) is in 'All of the following expressions are in the package com.ibm.alphablox.bloxbuilder.lib.expression', which is puzzling. I suspect the search is erroneous - though going to the SQL Manual and finding the functions listed there, DATEADD is conspicuously absent, so maybe it isn't.
So, you are going to have to manual bash for the DB2 syntax. But, if anything is going to work, it is likely to involve:
DATEADD(HOUR, -4, CURRENT_TIME)
rather than any square brackets. However, a somewhat more extensive search, including the RedBook on DB2 and Oracle Compatibility, does not show DATEADD as a function that is supported by DB2. So, the DATEADD route is doomed to ... have problems.
Since DB2 (still) doesn't have a proper (SQL standard) INTERVAL type, you are into investigating 'durations'. See DevX for an explanation - but beware the number of cookies the site '.qnsr.com' wants to set. And read the manuals at the DB2 Info Centre.