计算Oracle SQL中2个日期/时间之间的差异 [英] Calculate difference between 2 date / times in Oracle SQL

问题描述

我有一张桌子，如下:

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

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

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

谢谢

推荐答案

您可以在Oracle中减去日期.这将使您的天数有所不同.乘以24得到小时，依此类推.

You can substract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on.

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

---

注意:

此答案适用于由Oracle数据类型DATE表示的日期. Oracle还具有数据类型TIMESTAMP，该数据类型也可以表示日期(带时间).如果减去TIMESTAMP值，则得到INTERVAL；否则，将得到INTERVAL.要提取数值，请使用EXTRACT函数.

This answer applies to dates represented by the Oracle data type DATE. Oracle also has a data type TIMESTAMP, which can also represent a date (with time). If you subtract TIMESTAMP values, you get an INTERVAL; to extract numeric values, use the EXTRACT function.

这篇关于计算Oracle SQL中2个日期/时间之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！