OraFAQ Forum: SQL & PL/SQL » timestamp shows 0 for all the records

Home » SQL & PL/SQL » SQL & PL/SQL » timestamp shows 0 for all the records (11g,toad 12.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

timestamp shows 0 for all the records [message #610241]

Tue, 18 March 2014 12:17

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

I have added a column to my table activity_date timestamp but record shows as

3/18/2014 1:08:05.000000 PM
3/18/2014 1:07:20.000000 PM
3/18/2014 1:05:39.000000 PM

Report message to a moderator

Re: timestamp shows 0 for all the records [message #610242 is a reply to message #610241]

Tue, 18 March 2014 12:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ashwanth77 wrote on Tue, 18 March 2014 10:17

I have added a column to my table activity_date timestamp but record shows as

3/18/2014 1:08:05.000000 PM
3/18/2014 1:07:20.000000 PM
3/18/2014 1:05:39.000000 PM

Good for you.
How can we assist?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: timestamp shows 0 for all the records [message #610244 is a reply to message #610241]

Tue, 18 March 2014 12:37

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Milliseconds is 0, not the timestamp.

Report message to a moderator

Re: timestamp shows 0 for all the records [message #610245 is a reply to message #610242]

Tue, 18 March 2014 12:38

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

I have added a column with timestamp to an existing table called log_entries.the purpose of these table and column is to capture the log entries with date and time.

activity_date timestamp this is column name
eg.when i run the sp ,the log entries should be inserted into this table,it is supposed to be as 3/18/2014 1:08:05.797201 PM but what i get is 3/18/2014 1:08:05.000000 PM for all records

Report message to a moderator

Re: timestamp shows 0 for all the records [message #610246 is a reply to message #610245]

Tue, 18 March 2014 12:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do you know & understand the difference between a DATE & TIMESTAMP datatype.
a DATE includes no fraction of a second.

if you populate a TIMESTAMP column with SYSDATE, no fractions exist

Report message to a moderator

Re: timestamp shows 0 for all the records [message #610285 is a reply to message #610246]

Wed, 19 March 2014 01:46

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

how to insert a sysdate with fractional sec into timestamp column

create table date_test
(
id number(2),
begin_date timestamp
);

insert into date_test values (1,TO_TIMESTAMP(sysdate, 'YYYY-MM-DD HH24:MI:SS'));

dis didnot work got the same result as

3/14/0019 12:00:00.000000 AM

and this worked
insert into date_test values (2,current_timestamp);

2 ,3/19/2014 2:53:39.954707 AM

thanks

[Updated on: Wed, 19 March 2014 01:57]

Report message to a moderator

Re: timestamp shows 0 for all the records [message #610297 is a reply to message #610285]

Wed, 19 March 2014 04:17

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You don't. Sysdate is a date, dates do not have fractional seconds. current_timestamp and systimestamp both return timestamps which do include factional seconds. So use one of them.

Report message to a moderator

Previous Topic:	Query taking Too much time
Next Topic:	SQL solution needed - urgent!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 07:28:02 CDT 2024