OraFAQ Forum: SQL & PL/SQL » Calculating in SQL based on data

Home » SQL & PL/SQL » SQL & PL/SQL » Calculating in SQL based on data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calculating in SQL based on data [message #11002]

Sun, 29 February 2004 17:49

JOHN
Messages: 182
Registered: April 1998

Senior Member

Hi everyone,

I have a database in which prices of stocks are stored daily. A program has been written to download the latest prices everyday and update the database with a timestamp stored as date stating when a price was last updated.

I would like to do a select statement which will show me all prices which havent had any new updates in the last 48 hours. How do I do this calculation on a data field using SQL?

Thanks, John

�

Report message to a moderator

Re: Calculating in SQL based on data [message #11010 is a reply to message #11002]

Mon, 01 March 2004 01:49

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

In Oracle, when you subtract one date from another, you get the result in days. Since 48 hours equals 2 days, you want the records where today's date (sysdate) minus the date last updated is greater than 2. Please see the example below.

scott@ORA92> ALTER SESSION SET NLS_DATE_FORMAT = 'dd-Mon-yyyy hh24:mi:ss'
  2  /

Session altered.

scott@ORA92> DESC stocks
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
 PRICE                                                          NUMBER
 LAST_UPDATED                                                   DATE

scott@ORA92> SELECT * FROM stocks
  2  /

        ID      PRICE LAST_UPDATED
---------- ---------- --------------------
         1         10 26-Feb-2004 02:52:59
         2         20 27-Feb-2004 01:52:59
         3         30 28-Feb-2004 00:52:59
         4         40 29-Feb-2004 02:52:59
         5         50 01-Mar-2004 03:52:59

scott@ORA92> SELECT SYSDATE FROM DUAL
  2  /

SYSDATE
--------------------
01-Mar-2004 03:52:59

scott@ORA92> SELECT * FROM stocks
  2  WHERE  SYSDATE - last_updated > 2
  3  /

        ID      PRICE LAST_UPDATED
---------- ---------- --------------------
         1         10 26-Feb-2004 02:52:59
         2         20 27-Feb-2004 01:52:59
         3         30 28-Feb-2004 00:52:59

Report message to a moderator

Previous Topic:	Using DBMS_LOCK.RELEASE to release the lock owned by another process
Next Topic:	displaying dates between two dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:07:49 CDT 2024