计算 Oracle SQL 中 2 个日期/时间之间的差异

2021-12-05 00:00:00 oracle date-arithmetic

我有一个表格如下:

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；要提取数值，请使用 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.

相关文章