Precision of CURRENT_TIMESTAMP on windows server

4151877

Currently I am running oracle test instance on a Windows 2008 R2 machine.

I am not able to increase the precision of the function CURRENT_TIMESTAMP higher than 3.

According to some stackoverflow answer https://stackoverflow.com/questions/6632816/java-and-oracle-fractional-precision  the precision is depending on the OS clock.

How can I increase the precision? Can I reconfigure the used clock and change to something else maybe?

Or is it a serios constraint, that on a windows machine oracle cannot get higher precision than 3 (which sounds actually quite sad in 2019)?

Gaz in Oz

Windows is "quite sad" in that if you use your favourite search engine and look up "windows display microseconds" or some such, you will see that windows themselves have been working on it for a while, and sqlserver only display to milliseconds...

Vague internet search for example:

https://duckduckgo.com/?q=windows+timestamp+microseconds&t=opera&ia=web

Oracle sources:

which references

https://support.oracle.com/rs?type=doc&id=340512.1

which mentions:

"8) What is SYSTIMESTAMP ?

You could say that SYSTIMESTAMP is "sysdate with time zone information added".
SYSTIMESTAMP is the timestamp on the server machine itself and is obtained on Unix platforms by calling " GetTimeOfDay " and on Windows by calling "GetSystemTime" to get the servers local time.  This means that SYSTIMESTAMP, just like SYSDATE depends on Unix platforms on the UNIX time configuration (= Unix TZ variable) for the Unix session when the database and listener where started.
The precision is platform dependant, on most Unix platforms it's microseconds (10-6) on Windows this is Milliseconds (10-3). "

...and as systimestamp, current_timestamp and localtimestamp all show a precision down to milliseconds on windows, with no db setting to increase the granularity to more than three, well, it kind of all speaks to the fact that Oracle on widows has an OS dependancy....

SQL> select systimestamp, current_timestamp, localtimestamp from dual;SYSTIMESTAMP                  CURRENT_TIMESTAMP             LOCALTIMESTAMP----------------------------- ----------------------------- -----------------------------2019-12-13 00:21:41.139000000 2019-12-13 00:21:41.139000000 2019-12-13 00:21:41.1390000001 row selected.SQL>

To get a definitive answer you would need to speak to Oracle themselves, but the evidence is "there".

Gaz in Oz

As it says "elsewhere" the limitation is OS dependant and on windows you are limited to a precision of 3.

If you ran a database in a VM on a supported linux OS, then perhaps you might be able to get a higher precision, but I've not got a linux vm running a db to test.

4151877

"As it says "elsewhere" the limitation is OS dependant" where does it say that? can you provide me a link to some documentation or so?

John Thorton

29402ebf-4d75-4c9f-9f7c-e09308a57aa3 wrote:"As it says "elsewhere" the limitation is OS dependant" where does it say that? can you provide me a link to some documentation or so?

GOOGLE is your friend, but first you must actually use it

https://lmgtfy.com/?q=windows+time+%20resolution

The default timer resolution on Windows is 15.6 ms – a timer interrupt 64 times a second. When programs increase the timer frequency they increase power consumption and harm battery life.

Cookiemonster76

I suspect "elsewhere" is a reference stackoverflow.

Gaz in Oz

Windows is "quite sad" in that if you use your favourite search engine and look up "windows display microseconds" or some such, you will see that windows themselves have been working on it for a while, and sqlserver only display to milliseconds...

Vague internet search for example:

https://duckduckgo.com/?q=windows+timestamp+microseconds&t=opera&ia=web

Oracle sources:

which references

https://support.oracle.com/rs?type=doc&id=340512.1

which mentions:

"8) What is SYSTIMESTAMP ?

You could say that SYSTIMESTAMP is "sysdate with time zone information added".
SYSTIMESTAMP is the timestamp on the server machine itself and is obtained on Unix platforms by calling " GetTimeOfDay " and on Windows by calling "GetSystemTime" to get the servers local time.  This means that SYSTIMESTAMP, just like SYSDATE depends on Unix platforms on the UNIX time configuration (= Unix TZ variable) for the Unix session when the database and listener where started.
The precision is platform dependant, on most Unix platforms it's microseconds (10-6) on Windows this is Milliseconds (10-3). "

...and as systimestamp, current_timestamp and localtimestamp all show a precision down to milliseconds on windows, with no db setting to increase the granularity to more than three, well, it kind of all speaks to the fact that Oracle on widows has an OS dependancy....

SQL> select systimestamp, current_timestamp, localtimestamp from dual;SYSTIMESTAMP                  CURRENT_TIMESTAMP             LOCALTIMESTAMP----------------------------- ----------------------------- -----------------------------2019-12-13 00:21:41.139000000 2019-12-13 00:21:41.139000000 2019-12-13 00:21:41.1390000001 row selected.SQL>

To get a definitive answer you would need to speak to Oracle themselves, but the evidence is "there".

Gaz in Oz

On win 10 home, Oracle Virtualbox 5.2.12 installed and running Oracle Linux 7.6 with Oracle 18c XE from here - Vagrant:

SQL> select systimestamp, current_timestamp, localtimestamp from dual;SYSTIMESTAMP                  CURRENT_TIMESTAMP             LOCALTIMESTAMP----------------------------- ----------------------------- -----------------------------2019-12-13 01:26:18.733360000 2019-12-13 01:26:18.733363000 2019-12-13 01:26:18.733363000SQL>

so if timestamp granularity to microseconds is important to you (and all the other reasons you should be running Oracle db on *NIX), then switch to a *NIX OS. As shown, even in the VM, you get better granularity.

4151877

well windows itself is able to resolve microseconds:
In PowerShell:
Get-Date -format yyyyMMddTHHmmssffffffZ

In SqlExpress:

SELECT SYSDATETIME();

It's really sad that oracle's using some mechanism probably from 1995 to retrieve the current time from the system.... well that's life. But still oracle sends you emails like "Don’t Just Look into the Future. Experience It." or "Take the Lead in Data".

Probably running Oracle on windows is just a bad idea :-((

EdStevens

4151877 wrote:<snip>Probably running Oracle on windows is just a bad idea :-((

Probably running .. Windows is just a bad idea.