Home » SQL & PL/SQL » SQL & PL/SQL » Time field creation
Time field creation [message #18550] Tue, 05 February 2002 05:45 Go to next message
Adebayo
Messages: 5
Registered: August 2001
Junior Member
How do I create a field that records the time of data inputed into a data base.

for a similar example the date is done this way

CREATE TABLE DEV_SKILL (
DEV_ID NUMBER NOT NULL,
SKILL_ID NUMBER NOT NULL,
DATE_FILLED DATE NOT NULL)

how do I create the time field
Re: Time field creation [message #18552 is a reply to message #18550] Tue, 05 February 2002 06:01 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
The date datatype already includes time so you don't need an extra time field. You just have to format the value correctly.

example:

to_date('01-jan-2002 22:10:59','DD-MON-YYYY HH24:MI:SS')

to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')

There are several ways to format a date input/output, for the different posibilities have a look at:
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/sql_elements4.htm#48515

HTH
Mike
Re: Time field creation [message #18554 is a reply to message #18550] Tue, 05 February 2002 07:05 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
You missed Mike's point. Just create the field as date (time is implicit), as follows:

1 CREATE TABLE DEV_SKILL (
2 DEV_ID NUMBER NOT NULL,
3 SKILL_ID NUMBER NOT NULL,
4 DATE_FILLED DATE ,
5* PRIMARY KEY ( SKILL_ID, DEV_ID) )
12:01:04 ==> /

Table created.

1* insert into dev_skill values (3,4, to_date('02/02/2001 13:14','mm/dd/yyyy hh24:mi'))
12:02:53 ==> /

1 row created.

1* select to_char(date_filled, 'mm/dd/yyyy hh24:mi') from dev_skill
12:03:32 ==> /

TO_CHAR(DATE_FIL
----------------
02/02/2001 13:14
Re: Time field creation [message #18568 is a reply to message #18550] Tue, 05 February 2002 08:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Are you trying to auto populate that field??
CREATE TABLE ttt (pk NUMBER NOT NULL, date_filled DATE );

CREATE TRIGGER ttt_bef_ins
   BEFORE INSERT
   ON ttt
   FOR EACH ROW
BEGIN
   IF (:NEW.date_filled IS NULL)
   THEN
      :NEW.date_filled := SYSDATE;
   END IF;
END;
/

INSERT INTO ttt (pk, date_filled)  VALUES (1, SYSDATE);

INSERT INTO ttt (pk) VALUES (2);

SELECT * FROM ttt;
PK                                      DATE_FILLED        
--------------------------------------- -------------------
1                                       2/4/02 10:47:11 PM 
2                                       2/4/02 10:47:15 PM 
2 rows selected
Re: Time field creation [message #18570 is a reply to message #18550] Tue, 05 February 2002 09:12 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
WRT my previous posting - you can just set the Default value in the table description - then no need for the trigger.
CREATE TABLE ttt (pk NUMBER NOT NULL, date_filled DATE Default sysdate);
Previous Topic: sql statement help
Next Topic: sql statement help
Goto Forum:
  


Current Time: Sat Sep 18 04:42:03 CDT 2021