Timestamp field?

[lbrechler]

I'm new to Oracle and still trying to get my bearings, so please forgive me if this is a dumb questions. I am creating a log table for my application that will reside in my Oracle database. I'd like to have Oracle automatically update each row's timestamp field as it is INSERTed into the table. Is there an easy way to do this? Trigger? Sequence?

Also, I'd like to make sure that it stores both the date AND time (don't mind if this is milliseconds or not) -- my thought is to use the same field for both, but some posts have mentioned having separate "Date" and "Time" fields -- any strong arguments for one or the other?

Thanks,
~lbrechler

 

[THoey]

If you use a date field, you get both the date and time. Your settings may not be displaying it, but it is there. try the following on your date field:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL; Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

Well, what I also wanted to add is that Oracle already has it's own Audit Trail that you can use for this. I haven't used it yet, but you should be able to read up on it in the manuals. Why re-invent the wheel... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[lbrechler]

what would a similar insert statement look like? I tried the following:

INSERT into LOG (USER_ID, TIMESTAMP, ACTIVITY_ID) values (77, '2002-06-11 12:14:51', 1)

and it gives me the error "ORA 01830: date format picture ends before converting entire input string"

Any suggestions?

 

[lbrechler]

Well, after way too many hours looking at this -- this is how it will be:

The INSERT statement inserts the "sysdate" (date AND time)with no formatting string, which is fine, because I just want it all in there.

INSERT into LOG (USER_ID, TIMESTAMP, ACTIVITY_ID) values (77, sysdate, 1)

If and when I eventually write something to SELECT the information from that field, I will worry about the formatting then.

Hope this helps someone else out there.

~Lindsay

 

[Guest_imported]

It looks like you already have what you want but I wanted
to add that I've commonly had entrydate and lastupdate
DATE columns in my tables and use before row-statement triggers to set the appropriate value to sysdate.
One of the nice things about the Oracle DATE type is the
convenience of date arithmetic rather than more expensive
and cumbersome string manipulation.

 

[spenglerr]

Yes lbrechler,

but what if you want to insert not the actual date, then your insert needs the to_date() function:

INSERT into LOG (USER_ID, TIMESTAMP, ACTIVITY_ID) values (77, to_date('2002-06-11 12:14:51', 'yyyy-mm-dd hh24:mi:ss'), 1)

that's it