Forum Stats

  • 3,875,494 Users
  • 2,266,929 Discussions
  • 7,912,230 Comments

Discussions

Precision of CURRENT_TIMESTAMP on windows server

4151877
4151877 Member Posts: 5
edited Dec 13, 2019 9:54AM in SQL & PL/SQL

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)?

Tagged:
Joerg.Sobottka

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Dec 12, 2019 9:45AM Answer ✓

    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".

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Dec 12, 2019 7:08AM

    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.

    Joerg.Sobottka
  • 4151877
    4151877 Member Posts: 5
    edited Dec 12, 2019 8:32AM

    "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
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 12, 2019 9:11AM
    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
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Dec 12, 2019 9:17AM

    I suspect "elsewhere" is a reference stackoverflow.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Dec 12, 2019 9:45AM Answer ✓

    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
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Dec 12, 2019 10:52AM

    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
    4151877 Member Posts: 5
    edited Dec 13, 2019 8:00AM

    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
    EdStevens Member Posts: 28,778 Gold Crown
    edited Dec 13, 2019 9:54AM
    4151877 wrote:<snip>Probably running Oracle on windows is just a bad idea :-((

    Probably running .. Windows is just a bad idea.