0

Is it possible in PLSQL convert date to number of milliseconds?

I tried

select to_number(TO_CHAR(sysdate, 'yyyymmddhh24miss')) * (24 * 60 * 60 * 1000) from dual;

but I get back 1743604888943174400000

which is not the same as java GetTime method https://www.w3resource.com/java-tutorial/util/date/java_date_gettime.php

mbrc
  • 3,523
  • 13
  • 40
  • 64
  • 1
    The answer is here: [https://stackoverflow.com/questions/7147982/oracle-systimestamp-sysdate-to-milliseconds](https://stackoverflow.com/questions/7147982/oracle-systimestamp-sysdate-to-milliseconds) – swmcdonnell Jun 12 '18 at 14:16
  • 1
    Possible duplicate of [oracle systimestamp (sysdate) to milliseconds](https://stackoverflow.com/questions/7147982/oracle-systimestamp-sysdate-to-milliseconds) – swmcdonnell Jun 12 '18 at 17:07

2 Answers2

0

As swmcdonnell already said, your question is kind of dublicated from: oracle systimestamp (sysdate) to milliseconds

But you got an understanding problem. I did split your query into three columns.

SELECT TO_CHAR (SYSDATE, 'yyyymmddhh24miss'), -- Here we convert our sysdate into a 'string'
       TO_NUMBER (TO_CHAR (SYSDATE, 'yyyymmddhh24miss')), -- this will output the resulting string as number - it will look the same as the 1st column
       TO_NUMBER (TO_CHAR (SYSDATE, 'yyyymmddhh24miss')) * (24 * 60 * 60 * 1000) -- here you calculated 20180613150101 * 24 * 60... i think that's not what you want to do
  FROM DUAL;

This doesn't make sense. If you want the 'total-milliseconds' you have to:

  1. use current_timestamp instead of sysdate
  2. multiply the year (yyyy) by 365 days * 24 hours * 60 minutes * 60 seconds * 1000 ms
  3. multiply the day-of-year (DDD) by 24 hours * 60 minutes * 60 seconds * 1000 ms
  4. multiply the hour * 60 minutes * 60 seconds * 1000 ms
  5. multiply the minute * 60 seconds * 1000 ms
  6. multiply the second * 1000 ms
  7. add ms

This would result in something like this:

SELECT to_number(TO_CHAR (CURRENT_TIMESTAMP, 'yyyy')) * 365 * 24 * 60 * 60 * 1000
     + to_number(TO_CHAR (CURRENT_TIMESTAMP, 'DDD')) * 24 * 60 * 60 * 1000
     + to_number(TO_CHAR (CURRENT_TIMESTAMP, 'HH24')) * 60 * 60 * 1000
     + to_number(TO_CHAR (CURRENT_TIMESTAMP, 'mi')) * 60 * 1000
     + to_number(TO_CHAR (CURRENT_TIMESTAMP, 'ss')) * 1000
     + to_number(TO_CHAR (CURRENT_TIMESTAMP, 'FF'))
  FROM DUAL;

.. But what are u going to do with it? I don't know Java but a Timestamp should not be displayed as interval. This would be something like interval in oracle or a timespan in C#.

kara
  • 3,205
  • 4
  • 20
  • 34
0
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHHMISS'))||to_number(SUBSTR(TO_CHAR (CURRENT_TIMESTAMP, 'FF'),0,2)) FROM dual;
Jakye
  • 6,440
  • 3
  • 19
  • 38
Halil özkul
  • 1