Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    35

    Unanswered: DB2 Retrive last hour record

    Need to get all records for last 1 , 2 hour

    Code:
    select * from A where B = current date  - 1 hour
    Does not work.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •