Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Question Unanswered: Query question: retrieving data with timestamp range

    have the following sql for Oracle which i want to convert to use with DB2:

    /**
    * Oracle SQL that returns session IDENTIFIER value by USERID and between SYSDATE and SYSDATE - 5 minutes
    */
    private static final String lastsessn_sql1 = "select IDENTIFIER from BCHISTSESSN " +
    "where USERID = ? " +
    "and STARTDT between (SYSDATE - 5/1440) " +
    "and SYSDATE " +
    "order by STARTDT DESC";

    i've played around with the db2 syntax with no luck so far. i know that i can get the db2 instance's system time as such:

    select distinct(CURRENT TIMESTAMP) from SYSIBM.SYSTABLES
    which returns: Nov 19. 2004 11:29:30 AM 771001

    if i use the following, nothing is returned, although i know there is valid activity within the 5 minute time period for this user:
    select IDENTIFIER from bcHistSessn
    where USERID = 694005277882043342185161000000
    and (char(STARTDT) = (CURRENT TIMESTAMP - 5 MINUTES))

    as of know, i've run out of ideas so any help would be appreciated.

    thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jarmy
    select IDENTIFIER from bcHistSessn
    where USERID = 694005277882043342185161000000
    and (char(STARTDT) = (CURRENT TIMESTAMP - 5 MINUTES))
    is looking for something that happened at exactly that microsecond five minutes ago. What you probably wanted was more like
    Code:
    select IDENTIFIER from bcHistSessn
    where USERID = 694005277882043342185161000000
    and STARTDT >= CURRENT TIMESTAMP - 5 MINUTES
    See, you don't need to use CHAR() either

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    that worked perfectly. thank you!!

Posting Permissions

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