Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Procedures using time (SQL Developer, 11g, XP)
PL/SQL Procedures using time [message #358068] Sat, 08 November 2008 03:17 Go to next message
kimstar00
Messages: 4
Registered: November 2008
Junior Member
hello all,

i need some help with using time in my table. how do i insert a time (what data type do i use, and what does the INSERT statement look like), eg. i want time like 1:00:00am, 2:00:00pm in my receiveTime column.

i have a table:
ID receiveTime
-- ----
1 need a time
2 need a time
3 need a time

And also, how would i write a condition for this procedure, the condition is that i want to compare my System Time with the Time in the receiveTime column.
eg. if system time > receive time THEN blah blah;
Re: PL/SQL Procedures using time [message #358073 is a reply to message #358068] Sat, 08 November 2008 04:40 Go to previous messageGo to next message
kimstar00
Messages: 4
Registered: November 2008
Junior Member
now i'm using the TIMESTAMP data type for the receiveTime column.

Now how do i compare the time in the receiveTime column and my SYSTIMESTAMP? for example:

IF receiveTime > SYSTIMESTAMP
THEN blah
Re: PL/SQL Procedures using time [message #358091 is a reply to message #358073] Sat, 08 November 2008 07:18 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
how do i insert a time (what data type do i use, and what does the INSERT statement look like)

It depends on how precise you want to be; SYSDATE offers time up to seconds, so - if you choose a DATE column, you may do that in such a manner:
SQL> create table test
  2  (id number,
  3   receive_time date
  4  );

Table created.

SQL> insert into test (id, receive_time) values (1, sysdate);

1 row created.

SQL> insert into test (id, receive_time) values (1, sysdate);

1 row created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select * from test;

        ID RECEIVE_TIME
---------- -------------------
         1 08.11.2008 14:12:44
         1 08.11.2008 14:12:50

SQL>


Quote:
how do i compare the time in the receiveTime column and my SYSTIMESTAMP
It depends on what you want to do by this comparison; you may, for example, fetch all records whose "receive_time" is "before now (SYSDATE)", such as
SQL> select * from test where receive_time < sysdate;

        ID RECEIVE_TIME
---------- -------------------
         1 08.11.2008 14:12:44
         1 08.11.2008 14:12:50

SQL>
Or, if you'd like to do something in a record-by-record manner, you could write a PL/SQL script, use a cursor for loop and do something, such as
SQL> begin
  2    for cur_r in (select id, receive_time from test)
  3    loop
  4      if cur_r.receive_time < sysdate then
  5         dbms_output.put_line('receive_time < sysdate');
  6      else
  7         dbms_output.put_line('receive_time >= sysdate');
  8      end if;
  9    end loop;
 10  end;
 11  /
receive_time < sysdate
receive_time < sysdate

PL/SQL procedure successfully completed.

SQL>

Or, if none of these fits your needs, what are your needs, then?
Previous Topic: Dynamic Join
Next Topic: sql script issue.. Please help
Goto Forum:
  


Current Time: Fri Dec 09 04:12:24 CST 2016

Total time taken to generate the page: 0.16136 seconds