ORACLE converts data between milliseconds and dates, and oracle converts data between milliseconds.
Convert millisecond to date
SELECT TO_CHAR(1406538765000 / (1000 * 60 * 60 * 24) +
TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') AS CDATE
FROM DUAL;
Date conversion in milliseconds
SELECT TO_NUMBER(TO_DATE('2014-07-28 17:12:45', 'YYYY-MM-DD HH24:MI:SS') -
TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000
FROM DUAL;
Obtain the current system time
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
Note: The time for converting millisecond to date format can be 12-hour or 24-hour.
In oracle, how does one convert the system time to milliseconds?
This problem ....
Let me give you a detailed answer, so I will not worry about any questions in this regard.
Oracle Computing Time Difference Expression
-- Obtain the number of seconds of the difference between the two time periods
Select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss')-To_date ('2017-04-30 23:59:59 ', 'yyyy-mm-dd hh24-mi-ss ') * 24*60*60*1000) the number of seconds from dual;
/*
Difference in luxury seconds
----------
86401000
1 row selected
*/
-- Obtain the time difference in seconds
Select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss')-To_date ('2017-04-30 23:59:59 ', 'yyyy-mm-dd hh24-mi-ss ') * 24*60*60) Time Difference from dual;
/*
Difference in seconds
----------
86401
1 row selected
*/
-- Get the minute-to-minute difference between the two time periods
Select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss')-To_date ('2017-04-30 23:59:59 ', 'yyyy-mm-dd hh24-mi-ss ') * 24*60) number of minutes from dual;
/*
Minutes of difference
----------
1441
1 row selected
*/
-- Get the hours of difference between the two time periods
Select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss')-To_date ('2017-04-30 23:59:59 ', 'yyyy-mm-dd hh24-mi-ss ') * 24) hours from dual;
/*
Time Difference
----------
25
1 row selected
*/
-- Get the number of days of the difference between the two time periods
Select ceil (To_date ('2017-05-02 00:00:00 ', 'yyyy-mm-dd hh24-mi-ss')-To_date ('2017-04-30 23:59:59 ', 'yyyy-mm-dd hh24-mi-ss ') days from dual;
/*
Days difference
----------
2
1 row selected
*/
----------------------------------------
Note: The number of days can be directly reduced by two days, which is more convenient.
----------------------------------------
-- Get the two-time month difference
Select (EXTRACT (year ...... remaining full text>
Conversion of oracle date data
SQL> select extract (day from (to_date ('2017-05-11 15:10:24 ', 'yyyy-mm-dd hh24: mi: ss ') -to_date ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') day (0) to second (0 )) * 24*60*60
2 + extract (hour from (to_date ('2017-05-11 15:10:24 ', 'yyyy-mm-dd hh24: mi: ss ') -to_date ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') day (0) to second (0) * 60*60
3 + extract (minute from (to_date ('2017-05-11 15:10:24 ', 'yyyy-mm-dd hh24: mi: ss ') -to_date ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') day (0) to second (0) * 60
4 + extract (second from (to_date ('2017-05-11 15:10:24 ', 'yyyy-mm-dd hh24: mi: ss ') -to_date ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') day (0) to second (0 ))
5 from dual;
EXTRACT (DAYFROM (TO_DATE ('2017-
------------------------------
1336749024
SQL> select to_date ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') + numtodsinterval (1970, 'second') from dual;
TO_DATE ('2017-01-0100:00:00 ','
------------------------------
03:10:24
SQL>
The above are mutually verified. If it is helpful, please adopt