Home » SQL & PL/SQL » SQL & PL/SQL » TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion (Oracle 10G windows XP)
TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577233] Wed, 13 February 2013 11:13 Go to next message
skumari
Messages: 7
Registered: January 2013
Location: NY
Junior Member
I have column and loading time stamp like below.

RECORDTIMESTAMP TIMESTAMP DEFAULT to_timestamp(to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS')||'.0', 'DD-Mon-YYYY HH24:MI:SS.FF1') NOT NULL ENABLE

But RECORDTIMESTAMP data looks like '4/8/2009 5:48:00.000000 PM'
I would like to supress 3 zeros in milli seconds section. Please advise.
( 3 more zero in milli seconds section)
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577234 is a reply to message #577233] Wed, 13 February 2013 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
would like to supress 3 zeros in milli seconds section. Please advise.
( 3 more zero in milli seconds section)


What does that mean? It suppresses 0 and there are 0 in addition!!!

By the way, it does EXACTLY what you asked it to do.

0.0 s = 0.000000 s

Regards
Michel
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577260 is a reply to message #577234] Wed, 13 February 2013 15:41 Go to previous messageGo to next message
Littlefoot
Messages: 19610
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think you are mistaken here; default value looks suspicious - why do you do all that conversions? All you have to do is this:
SQL> create table test
  2    (id    number,
  3     ts    timestamp   default systimestamp);

Table created.

Now, inserting some data:
SQL> insert into test (id) values (1);

1 row created.

SQL> column ts format a30
SQL> select * from test;

        ID TS
---------- ------------------------------
         1 13.02.13 22:35:41,248000

OK, that's fine, but you don't want "248000" but "248"; right? In that case, supply a format mask while SELECTING values (not while INSERTING!):
SQL> select id, to_char(ts, 'dd.mm.yyyy hh24:mi:ss:ff3') ts from test;

        ID TS
---------- ------------------------------
         1 13.02.2013 22:35:41:248

SQL>

Because, I believe that Oracle stores timestamps in its own format (not human readable) and all you can (and should) do is: don't care about it! Let Oracle care about storing values; you - take care about displaying them. And here's where format masks come into the game.

If you think "hey, why wouldn't I store my timestamps into a VARCHAR2 column, keeping that format instantly, when I insert values" - don't even think about it. Storing dates / timestamps into VARCHAR2 column is - in most cases - stupid and brings problems in the future.

[Updated on: Wed, 13 February 2013 15:42]

Report message to a moderator

Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577262 is a reply to message #577260] Wed, 13 February 2013 16:36 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Just use "cast(sysdate as timestamp)":
SQL> create table xt_timestamp (tm timestamp default cast(sysdate as timestamp));

Table created.

Elapsed: 00:00:01.13
SQL> insert into xt_timestamp values(default);

1 row created.

Elapsed: 00:00:00.04
SQL> select * from xt_timestamp;

TM
---------------------------------------------------------------------------
14-FEB-13 02.34.42.000000 AM

1 row selected.

Elapsed: 00:00:00.03
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577266 is a reply to message #577262] Wed, 13 February 2013 17:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Above all, why not use systimestamp if you want a timestamp and sysdate if you want a date?

Regards
Michel
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577269 is a reply to message #577266] Wed, 13 February 2013 17:57 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

I can find only one reason - sometimes can be inserts with normal timestamp with milliseconds
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577271 is a reply to message #577269] Wed, 13 February 2013 18:00 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Also main answer: declare column as timestamp(3)
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577272 is a reply to message #577271] Wed, 13 February 2013 18:16 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which does not prevent from having 6 decimals:
SQL> create table t (v timestamp(3));

Table created.

SQL> insert into t values (systimestamp);

1 row created.

SQL> select * from t;
V
-----------------------------------------
14/02/2013 01:15:28.218000

1 row selected.

Output depends ONLY on the default format unless you define a specific one with TO_CHAR.

Regards
Michel
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577274 is a reply to message #577272] Wed, 13 February 2013 22:40 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Of course, as with other datatypes like number(n), varchar2(n) etc... but i said about field sizing
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577286 is a reply to message #577274] Thu, 14 February 2013 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I was talking about OP's issue: "But RECORDTIMESTAMP data looks like '4/8/2009 5:48:00.000000 PM'" Smile

Regards
Michel
Re: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion [message #577311 is a reply to message #577286] Thu, 14 February 2013 03:31 Go to previous message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

OOps, sorry, i just was confused with "data looks" Smile I do not know why I thought that OP was talking about the data gotten with dump
Previous Topic: PipeLined Function
Next Topic: need help..
Goto Forum:
  


Current Time: Thu Sep 18 17:32:35 CDT 2014

Total time taken to generate the page: 0.14041 seconds