Example of mutual conversion between ORACLE milliseconds and dates, and oracle mutual conversion

Example of mutual conversion between ORACLE milliseconds and dates, and oracle mutual conversion

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.

How Does oracle convert the current date to milliseconds?

The difference between the two numbers can be calculated by day.
Select to_char (sysdate, 'yyyymmdd')-to_char (date column, 'yyyymmdd') from table;
If millisecond is to be calculated, it can be converted to an hour, then minute, and second, and then * 1000 is milliseconds. 1 second = 1000 milliseconds.

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>