ORACLE converts data between milliseconds and dates, and oracle converts data between milliseconds.

Source: Internet
Author: User

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

Related Article

Contact Us

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.