Oracle-查询两个日期之间的时差 [英] Oracle - Query the difference between two dates

问题描述

我正在尝试在Oracle中提出一种方法来计算日期之间的天数差异.

简而言之.我有两个数据字段"begins_at"和"ends_at"，这些字段的DATA_TYPE是"TIMESTAMP(6)"

例如.我有以下情况:

"begins_at"值为"13年3月26日02.50.00.000000000 PM"

"ends_at"值为"13年3月30日09.25.00.000000000 PM"

我正在尝试计算以天为单位的两个时间戳之间的差异.在Oracle中有可能吗?

我尝试了以下语句，但抛出了错误:

Select TO_DATE(begins_at, 'YYYYMMDD HH:MI:SS AM') - TO_DATE(ends_at, 'YYYYMMDD   HH:MI:SS AM') day_diff

From dual

任何提示都将不胜感激.

解决方案

正如我们的匿名马友所说，抛出错误"没有帮助.但是，您很可能会收到ORA-01830，日期格式图片在转换整个输入字符串之前结束"或ORA-1858，在预期有数字的地方找到了非数字字符"或类似的内容.

to_date函数采用一个char参数，因此当您将timestamp字段传递给它时，会发生隐式转换；您实际上是在做:

to_date(to_char(begins_at, 'DD-MON-RR'), 'YYYYMMDD HH:MI:SS AM')

...仅使用默认的NLS设置；我猜您在此示例中将NLS_DATE_FORMAT设置为"DD-MON-RR"；确切的值将影响您得到的错误. timestamp以字符串形式给出的格式与您为to_date指定的格式不匹配.

您永远不要依赖隐式转换，但是您根本不需要在这里进行转换.可以直接比较timestamp值，并在文档中中解释结果.只需从另一个值中减去一个值即可得到 interval 数据类型:

select ends_at - begins_at from t42;

ENDS_AT-BEGINS_AT
---------------------------------------------------------------------------
+000000004 06:35:00.000000

如果您只需要一整天，可以使用 extract :

从t42中选择摘录(从endsat-days开始的日期)；

EXTRACT(DAYFROMENDS_AT-BEGINS_AT)
---------------------------------
                                4

您还可以使用 cast :

select cast(ends_at as date) - cast(begins_at as date) from t42;

CAST(ENDS_ATASDATE)-CAST(BEGINS_ATASDATE)
-----------------------------------------
                               4.27430556

我不确定您为什么要使用timestamp. Oracle date类型包括时间部分，直至一秒的精度.由于您(当前)似乎并未使用时间戳的小数部分，因此坚持使用date可能会使您的操作更简单，但我想这取决于您稍后打算对这些字段进行的处理.

I am trying to come up with a way in Oracle to calculate the difference between dates in days.

In short. I have two data fields "begins_at" and "ends_at" and the DATA_TYPE for those fields is "TIMESTAMP(6)"

For example. I have the following scenario:

"begins_at" value as "26-MAR-13 02.50.00.000000000 PM"

"ends_at" value as "30-MAR-13 09.25.00.000000000 PM"

I am trying to calculate the different between the two timestamps in days. Is it possible in Oracle?

I tried the following statement but threw an error:

Select TO_DATE(begins_at, 'YYYYMMDD HH:MI:SS AM') - TO_DATE(ends_at, 'YYYYMMDD   HH:MI:SS AM') day_diff

From dual

Any tip is much appreciated.

解决方案

As our anonymous equine friend noted, 'threw an error' is not helpful. However, it's likely you are getting an ORA-01830, 'date format picture ends before converting entire input string', or ORA-1858, 'a non-numeric character was found where a numeric was expected', or something similar.

The to_date function takes a char argument, so when you pass your timestamp field to it there is an implicit conversion happening; you're essentially doing:

to_date(to_char(begins_at, 'DD-MON-RR'), 'YYYYMMDD HH:MI:SS AM')

... only using your default NLS settings; I'm guessing you're NLS_DATE_FORMAT is set to 'DD-MON-RR' for this example; the exact value will affect the error you get. The format that your timestamp is being given as a string does not match the format you specified for to_date.

You should never rely on implicit conversions, but you don't need to do a conversion here at all. timestamp values can be compared directly, and the results are explained in the documentation. Just subtracting one value form the other will give you the answer as an interval data type:

select ends_at - begins_at from t42;

ENDS_AT-BEGINS_AT
---------------------------------------------------------------------------
+000000004 06:35:00.000000

If you just want the whole day portion you can use extract:

select extract (day from ends_at - begins_at) from t42;

EXTRACT(DAYFROMENDS_AT-BEGINS_AT)
---------------------------------
                                4

You can also treat the timestamp as date using cast:

select cast(ends_at as date) - cast(begins_at as date) from t42;

CAST(ENDS_ATASDATE)-CAST(BEGINS_ATASDATE)
-----------------------------------------
                               4.27430556

I'm not sure why you're using timestamp at all though. The Oracle date type includes a time portion, down to a precision of a second. As you don't (currently) seem to be using the fractional part of the timestamp, sticking to date might make your manipulations simpler, but I guess it depends what you intend to do with these fields later.

这篇关于Oracle-查询两个日期之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！