SQL Oracle - 查询两个日期之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15755714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - Query the difference between two dates
提问by Haitham Salah
I am trying to come up with a way in Oracle to calculate the difference between dates in days.
我想在 Oracle 中想出一种方法来计算以天为单位的日期之间的差异。
In short. I have two data fields "begins_at" and "ends_at" and the DATA_TYPE for those fields is "TIMESTAMP(6)"
简而言之。我有两个数据字段“begins_at”和“ends_at”，这些字段的 DATA_TYPE 是“TIMESTAMP(6)”
For example. I have the following scenario:
例如。我有以下场景：
"begins_at" value as “26-MAR-13 02.50.00.000000000 PM”
“begins_at”值为“26-MAR-13 02.50.00.000000000 PM”
"ends_at" value as “30-MAR-13 09.25.00.000000000 PM”
“ends_at”值为“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?
我正在尝试以天为单位计算两个时间戳之间的差异。在 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.
任何提示都非常感谢。
回答by Alex Poole
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.
正如我们匿名的马友所指出的那样，“抛出错误”没有帮助。但是，您可能会遇到 ORA-01830、“日期格式图片在转换整个输入字符串之前结束”或 ORA-1858、“在需要数字的地方发现非数字字符”或类似问题。
The to_datefunctiontakes a charargument, so when you pass your timestampfield to it there is an implicit conversion happening; you're essentially doing:
该to_date函数接受一个char参数，因此当您将timestamp字段传递给它时，会发生隐式转换；你基本上是在做：
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 timestampis being given as a string does not match the format you specified for to_date.
...仅使用您的默认 NLS 设置；我猜你在这个例子中将 NLS_DATE_FORMAT 设置为“DD-MON-RR”；确切的值会影响你得到的错误。您timestamp以字符串形式提供的格式与您为 指定的格式不匹配to_date。
You should never rely on implicit conversions, but you don't need to do a conversion here at all. timestampvalues 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 intervaldata type:
您永远不应该依赖隐式转换，但您根本不需要在这里进行转换。timestamp值可以直接比较，结果在文档中解释。只需从另一个值中减去一个值，您就会得到作为interval数据类型的答案：
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:
如果你只想要一整天的部分，你可以使用extract：
select extract (day from ends_at - begins_at) from t42;
从 t42 中选择提取物（从ends_at 到begins_at 的日期）；
EXTRACT(DAYFROMENDS_AT-BEGINS_AT)
---------------------------------
4
You can also treat the timestampas dateusing cast:
您还可以将timestamp视为date使用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 timestampat all though. The Oracle datetype 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 datemight make your manipulations simpler, but I guess it depends what you intend to do with these fields later.
我不确定你为什么要使用timestamp。Oracledate类型包括时间部分，精确到秒。由于您（目前）似乎没有使用时间戳的小数部分，因此坚持使用date可能会使您的操作更简单，但我想这取决于您以后打算对这些字段做什么。
回答by Art
Days, hours, minutes, seconds diff. example - copy/paste to see the output. Replace dates with your dates...:
天、小时、分钟、秒差异。示例 - 复制/粘贴以查看输出。用您的日期替换日期...：
SELECT start_date, end_date, time_diff,
EXTRACT(DAY FROM time_diff) days,
EXTRACT(HOUR FROM time_diff) hours,
EXTRACT(MINUTE FROM time_diff) minutes,
EXTRACT(SECOND FROM time_diff) seconds
FROM
(
Select start_date, end_date, (end_date - start_date) time_diff
From
(
Select CAST(to_date('01/01/2012 10:00:00 am', 'dd/mm/yyyy hh:mi:ss am') AS TIMESTAMP) end_date
, CAST(to_date('01/01/2012 07:00:00 am', 'dd/mm/yyyy hh:mi:ss am') AS TIMESTAMP) start_date
From dual
)
)
/
Subtract time only example:
仅减去时间示例：
SELECT trunc(mydate / 3600) hr
, trunc(mod(mydate, 3600) / 60) mnt
, trunc(mod(mydate, 3600) / 60 /60) sec
FROM
(
SELECT (to_date('01/03/2012 10:00:00', 'mm/dd/yyyy hh24:mi:ss') -
to_date('01/01/2012 07:00:00', 'mm/dd/yyyy hh24:mi:ss')) * 86400 mydate
FROM dual
)
/
HR MNT SEC
-------------
51 0 0
回答by Eric Jablow
See http://docs.oracle.com/cd/E17952_01/refman-5.1-en/date-and-time-functions.html#function_datediff. It describes DATEDIFF, which returns the difference in days, treating only the date values.
请参阅http://docs.oracle.com/cd/E17952_01/refman-5.1-en/date-and-time-functions.html#function_datediff。它描述DATEDIFF，它返回天数差异，仅处理日期值。
