DB2 Retrive last hour record

dinjo_jo · #1 (**permalink**) 01-06-10, 04:04

Need to get all records for last 1 , 2 hour

Code:

select * from A where B = current date  - 1 hour

Does not work.

tonkuma · #2 (**permalink**) 01-06-10, 06:33

What does "Does not work" mean?
Get error?
Get incorrect result?

What is the data type of column B?
timestamp?

This may(or may not) produce your required result.

select * from A where B >= current timestamp - 1 HOUR - MINUTE(current timestamp)MINUTES - SECOND(current timestamp)SECONDS - MICROSECOND(current timestamp)MICROSECONDS

tonkuma · #3 (**permalink**) 01-06-10, 06:36

Code:

------------------------------ Commands Entered ------------------------------
SELECT current timestamp
     , current timestamp - 1 HOUR - MINUTE(current timestamp)MINUTES - SECOND(current timestamp)SECONDS - MICROSECOND(current timestamp)MICROSECONDS
  FROM sysibm.sysdummy1;
------------------------------------------------------------------------------

1                          2                         
-------------------------- --------------------------
2010-01-06-20.35.19.267000 2010-01-06-19.00.00.000000

  1 record(s) selected.

stolze · #4 (**permalink**) 01-06-10, 14:26

Your problem is that CURRENT DATE returns a date (year, month, day) - not a timestamp (date + time). So you should use CURRENT TIMESTAMP as Tonkuma suggested.

Internet Services

Web Development

Digital Marketing

Consumer Tech