Query question: retrieving data with timestamp range

jarmy · #1 (**permalink**) 11-19-04, 14:53

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!

n_i · #2 (**permalink**) 11-19-04, 15:08

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

jarmy · #3 (**permalink**) 11-19-04, 17:14

that worked perfectly. thank you!!

Internet Services

Web Development

Digital Marketing

Consumer Tech