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

Source: Internet
Author: User

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>

Related Keywords:
Related Article

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

## A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• #### Sales Support

1 on 1 presale consultation

• #### After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.