oracle - 在Oracle SQL中计算2个日期/时间之间的差异(Calculate difference between 2 date / times in Oracle SQL)

Question

I have a table as follows:

(我有一张桌子，如下所示：)

Filename - varchar
Creation Date - Date format dd/mm/yyyy hh24:mi:ss
Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss

How can I calcuate the difference in hours minutes and seconds (and possibly days) between the two dates in Oracle SQL?

(如何计算Oracle SQL中两个日期之间的小时，分??钟和秒（可能还有几天）之间的差异？)

Thanks

(谢谢)

  ask by Steve translate from so

Answer 1

You can substract dates in Oracle.

(您可以在Oracle中减去日期。)

This will give you the difference in days.

(这将使您的天数有所不同。)

Multiply by 24 to get hours, and so on.

(乘以24得到小时，依此类推。)

SQL> select oldest - creation from my_table;

If your date is stored as character data, you have to convert it to a date type first.

(如果日期存储为字符数据，则必须先将其转换为日期类型。)

SQL> select 24 * (to_date('2009-07-07 22:00', 'YYYY-MM-DD hh24:mi') 
             - to_date('2009-07-07 19:30', 'YYYY-MM-DD hh24:mi')) diff_hours 
       from dual;

DIFF_HOURS
----------
       2.5

---

Note :

(注意事项 ：)

This answer applies to dates represented by the Oracle data type DATE .

(此答案适用于由Oracle数据类型DATE表示的DATE 。)

Oracle also has a data type TIMESTAMP , which can also represent a date (with time).

(Oracle还有一个数据类型TIMESTAMP ，它也可以表示一个日期（带时间）。)

If you subtract TIMESTAMP values, you get an INTERVAL ;

(如果减去TIMESTAMP值，则会得到INTERVAL ；)

to extract numeric values, use the EXTRACT function.

(要提取数值，请使用EXTRACT函数。)