Home » SQL & PL/SQL » SQL & PL/SQL » Adding a millisecond to timestamp (Oracle 10g)
Adding a millisecond to timestamp [message #324384] Mon, 02 June 2008 04:46 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,

I have the requirement that each row inserted in a loop should show the time when each record was inserted.
For this I have taken a column as timestamp.

While inserting the data for record in the table, I have to give a value for the timestamp column too.
I tried entrying (sysdate + 1 ) in that column.
But its giving
14:56:19 00:00:00
Value.

Kindly advice me a way as what to insert for the timestamp column as sysdate is not giving the appropriate result.

I have tried like this ....kindly let me know if I am correct or there is any other way to do this better:
CREATE TABLE local_test (col1 TIMESTAMP);

INSERT INTO local_test VALUES (LOCALTIMESTAMP);
INSERT INTO local_test VALUES (LOCALTIMESTAMP);
select * from local_test

Thanks in advance,
Mahi

[Updated on: Mon, 02 June 2008 04:55]

Report message to a moderator

Re: Adding a millisecond to timestamp [message #324390 is a reply to message #324384] Mon, 02 June 2008 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
create table timestamp_Test (col_1 number, col_2 timestamp);

insert into timestamp_test values (1,systimestamp);

select col_1,to_char(col_2,'dd-mon-yyyy hh24:mi:ss ff') from timestamp_test;
Re: Adding a millisecond to timestamp [message #324392 is a reply to message #324390] Mon, 02 June 2008 05:06 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
What happens if I use localtimestamp...whats the difference between two?
I have used localtimestamp.... what problems would I face with this...please let me know...coz systimestamp shows
02-JUN-08 03.28.32.643000 PM +05:30
while localtimestamp shows:
02-JUN-08 03.30.11.766000 PM

Thanks,
Mahi
Re: Adding a millisecond to timestamp [message #324395 is a reply to message #324392] Mon, 02 June 2008 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The difference is explained in Database SQL Reference.
Have a look to it and come back if there is something that you don't understand.

Regards
Michel
Re: Adding a millisecond to timestamp [message #324402 is a reply to message #324395] Mon, 02 June 2008 06:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair, I couldn't see anything that would generate a 100 second difference between the two fields.

Virmahi: Can you run the following sql in SQL*Plus and cut and paste the output:
select 'sys    ' src,to_char(systimestamp,'dd-mon-yyyy hh24:mi:ssxff') from dual union all
select 'local'   src,to_char(localtimestamp,'dd-mon-yyyy hh24:mi:ssxff') from dual union all
select 'current' src,to_char(current_timestamp,'dd-mon-yyyy hh24:mi:ssxff') from dual;
Re: Adding a millisecond to timestamp [message #324408 is a reply to message #324384] Mon, 02 June 2008 06:37 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
The output is :
SRC     TO_CHAR(SYSTIMESTAMP,'DD-MON-Y
------- ------------------------------
sys     02-jun-2008 16:56:44.271000
local   02-jun-2008 16:56:44.271000
current 02-jun-2008 16:56:44.271000


The example I gave has a difference of 100 seconds because I have run the query at that interval Smile

Re: Adding a millisecond to timestamp [message #324414 is a reply to message #324408] Mon, 02 June 2008 07:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Right.

So the difference that you're confused by is that the timestamp that has a Time Zone specified also shows the time zone (in this case +5:30), whereas the other doesn't.

Thanks for explaining that clearly in the first place.
Previous Topic: Information:How project database with Oracle to send on a server
Next Topic: How to provide access on procedure/functions to other users
Goto Forum:
  


Current Time: Sun Sep 21 23:15:47 CDT 2014

Total time taken to generate the page: 0.08720 seconds