Oracle-L: RE: sysdate - 90 mins query

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sat, 04 May 2002 07:43:19 -0800
Message-ID: <F001.0045803B.20020504074319@fatcity.com>

Srini,

If I understand correctly, you want all rows that are less than sysdate - 90 minutes. There are a lot of ways to approach this. I guess you might be getting stuck on a couple of points. You can convert the tm_date and tm_time columns into a real date with a time component -- e.g. "to_date(tm_date||tm_time,'YYYYMMDDHH24:MI')". The other key for you is to get the date minus 90 minutes. 24 hours time 60 minutes in an hour gives you the total number of minutes in a day. You can divide 90 by this number to give you the amount to subtract and the correct date/time:

1 select to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') now, 2 to_char(sysdate - 90/(24*60),'MM/DD/YYYY HH24:MI:SS') 3* from dual
SQL> /

NOW                 TO_CHAR(SYSDATE-90/
------------------- -------------------

05/04/2002 09:29:12 05/04/2002 07:59:12

So, with those two key pieces, you could write the query as follows:

  1 select *
  2 from tbl_tmstmp
  3 where to_date(tm_date||tm_time,'YYYYMMDDHH24:MI') <   4* to_date('20020504 03:00','YYYYMMDD HH24:MI') - 90/(24*60) SQL> / TMSTMP_ID TM_DATE TM_TIM
---------- ---------- ------

         1   20020502 12:30
         2   20020503 19:30
         3   20020503 23:30
         4   20020504 00:30

For your real query, simply replace "to_date('20020504 03:00','YYYYMMDD HH24:MI')" with sysdate.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of sarath
> kumar
> Sent: Saturday, May 04, 2002 9:38 AM
> To: Multiple recipients of list ORACLE-L
> Subject: sysdate - 90 mins query
>
>
> i hava a table tbl_tmstmp with date and time as 2
> different columns (tm_date number)and tm_time
> varchar2(6).
> select * from tbl_tmstmp;
> tmstmp_id tm_date tm_time
> -------- -------- -------
> 1 20020502 12:30
> 2 20020503 19:30
> 3 20020503 23:30
> 4 20020504 00:30
> 5 20020504 02:30
> 6 20020504 08:30
>
> i have requuirement to pickup the records which are
> sysdate - 90 minutes. Assuming the sysdate is 20020504
> and time is 03:00 AM i need to pick up the first 4
> records.
>
> is it possible without using the UNION as DBAS are
> picky about using UNIONS.
>
> thanks
> Srini.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sat May 04 2002 - 10:43:19 CDT