Search Smith

ColdFusion, SQL queries, and, of course, searching

Posts Tagged ‘milliseconds since midnight’

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.

Posted in Oracle, SQL | Tagged: , , | 1 Comment »