Search Smith

ColdFusion, SQL queries, and, of course, searching

Oracle: Getting milliseconds past midnight

Posted by David Faber on March 8, 2012

Do a search for oracle milliseconds past midnight and chances are you’ll see results for how to retrieve seconds past midnight:

SELECT TO_NUMBER( TO_CHAR( CURRENT_DATE, 'SSSSS' ) )
  FROM dual;

Finding the number of milliseconds that have elapsed since midnight (why would one do this? Well, perhaps to seed a random number generator) is actually fairly simple – use CURRENT_TIMESTAMP in place of CURRENT_DATE/SYSDATE:

SELECT TO_NUMBER( TO_CHAR( CURRENT_TIMESTAMP, 'SSSSSxFF' ) ) * 1000
  FROM dual;

You multiply by 1,000 as the result is returned as SSSSS.sss, where SSSSS is the number of seconds that have elapsed since midnight and sss is the number of milliseconds that have elapsed since the last second.

One Response to “Oracle: Getting milliseconds past midnight”

  1. […] Oracle: Getting milliseconds past midnight […]

Leave a Comment

Your email address will not be published. Required fields are marked *