Oracle computing time difference

Computing Time Difference in Oracle is often used. You can use "date 1-date 2" and calculate it to obtain the time difference: day, hour, minute, or second.

For example:

 

1. Select
2. TO_DATE ('2017-02-20 17:45:04 ', 'yyyy-mm-dd hh24: mi: ss')-TO_DATE ('2017-02-19 08:34:04 ', 'yyyy-mm-dd hh24: mi: ss') as Day
3. From dual;

select TO_DATE('2012-02-20 17:45:04','yyyy-mm-dd hh24:mi:ss')-TO_DATE('2012-02-19 08:34:04','yyyy-mm-dd hh24:mi:ss') as Day from dual;

Result:

The TO_DATE here is very useful, and it determines your time storage format.
To obtain the corresponding time unit, follow these steps:
1. In days

 

1. Round (to_number (end-date-start_date ))

round(to_number(end-date-start_date))

For example:

 

1. Select
2. Round (to_number (TO_DATE ('2017-02-20 17:45:04 ', 'yyyy-mm-dd hh24: mi: ss')-TO_DATE ('2017-02-19 08:34:04 ', 'yyyy-mm-dd hh24: mi: ss') as Day
3. From dual;

select round(to_number(TO_DATE('2012-02-20 17:45:04','yyyy-mm-dd hh24:mi:ss')-TO_DATE('2012-02-19 08:34:04','yyyy-mm-dd hh24:mi:ss'))) as Day from dual;

Result:

2. In hours

 

1. Round (to_number (end-date-start_date) * 24)

round(to_number(end-date-start_date)*24)

For example:

 

1. Select
2. Round (to_number (TO_DATE ('2017-02-20 17:45:04 ', 'yyyy-mm-dd hh24: mi: ss')-TO_DATE ('2017-02-19 08:34:04 ', 'yyyy-mm-dd hh24: mi: ss') * 24) as Hour
3. From dual;

select round(to_number(TO_DATE('2012-02-20 17:45:04','yyyy-mm-dd hh24:mi:ss')-TO_DATE('2012-02-19 08:34:04','yyyy-mm-dd hh24:mi:ss'))*24) as Hour from dual;

Result:

3. In minutes

1. Round (to_number (end-date-start_date) * 1440)

round(to_number(end-date-start_date)*1440)

For example:

1. Select
2. Round (to_number (TO_DATE ('2017-02-20 17:45:04 ', 'yyyy-mm-dd hh24: mi: ss')-TO_DATE ('2017-02-19 08:34:04 ', 'yyyy-mm-dd hh24: mi: ss') * 1440) as Minite
3. From dual;

select round(to_number(TO_DATE('2012-02-20 17:45:04','yyyy-mm-dd hh24:mi:ss')-TO_DATE('2012-02-19 08:34:04','yyyy-mm-dd hh24:mi:ss'))*1440) as Minite from dual;

Result:

Author: Poplar