PL/SQL Procedures using time [message #358068] |
Sat, 08 November 2008 03:17  |
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   |
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  |
 |
Littlefoot
Messages: 21823 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 asSQL> 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 asSQL> 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?
|
|
|