Looking for a timestamp showing milliseconds 2

[BarbaraFuhrmann]

Hi,

I know that

select to_char(sysdate, 'DD.MM.YYYY HH24:MI:SS') as stamp from dual;

results in

STAMP
-------------------
15.07.2005 10:42:36

How do I have to modify the command to achieve something like

STAMP
-----------------------
15.07.2005 10:42:36:067

??

Thank you
Barbara

 

[hoinz]

select to_char(current_timestamp, 'DD.MM.YYYY HH24:MI:SS:FF3') as stamp from dual;

 

[dbomrrsm]

what does FF3 stand for ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[hoinz]

3 is the scale of the fractional part:
milliseconds, therefore 3 ciphers right of the decimal point;
but I don't know the exact meaning of FF; must be in the docs.

 

[dbomrrsm]

and it goes up to 6 places

thanx hoinz

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

Yea, but (AFAIK) if you specify "FF6" the last three digits carry zeros, so granularity is still milliseconds.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbomrrsm]

santa

no they dont if you specify FF9 there are three zeros but if you specify FF6 all are populated no trailing 0's from 4-6

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

dbomrrsm, Perhaps you can help me understand what I must do to obtain more than millisecond granularity:

create table timestmp (x timestamp(9));
insert into timestmp values(systimestamp);
select * from timestmp;

X
-------------------------------
15-JUL-05 10.23.16.307000000 AM

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[hoinz]

Dave, for me the granularity is microseconds too, and I don't remember having done anything to achieve it.
Could it be an operating system issue?
regards

 

[dbomrrsm]

select
to_char(current_timestamp,
'DD.MM.YYYY HH24:MI:SS:FF6')
as stamp from dual;

16.07.2005 08:06:09:564685

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

Hoinz and DBomrrsm, As Hoinz suspected, accuracy beyond milliseconds appears to be o/s related:

On Windows 2000:

select to_char(current_timestamp,
'DD.MM.YYYY HH24:MI:SS:FF6')
as stamp from dual;

STAMP
--------------------------
16.07.2005 16:11:54:734000

...and on Unix:

select
to_char(current_timestamp,
'DD.MM.YYYY HH24:MI:SS:FF6')
as stamp from dual;

STAMP
--------------------------
16.07.2005 16:12:23:073857

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[lewisp]

A star from me for the insight, Dave.

...and a question: What is the arrangement of databases you have used in your tests above? When you say 'on Windows 2000' do you mean the SQLPlus session is W2k and the database server is Unix, or is the database running on W2k as well?

 

[SantaMufasa]

Good question, Lewis. In both of my cases, above, the client session was on a Windows 2000 machine; the millisecond example was to a Windows 2000-based server; the microsecond example was to a Unix-based server.

When accessing SYSDATE in Oracle, the source is always the server, not the client.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.