oracle oracle中两个时间戳（以天为单位）之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10885119/
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
difference between two timestamps (in days) in oracle
提问by Scuba_Steve
SELECT MIN (snap_id) AS FIRST_SNAP,
MAX (snap_id) AS LAST_SNAP,
MIN (BEGIN_INTERVAL_TIME) AS FIRST_QUERY,
MAX (END_INTERVAL_TIME) AS LAST_QUERY,
max(end_interval_time) - min(begin_interval_time) as "TIME_ELAPSED"
FROM dba_hist_snapshot
ORDER BY snap_id;
2931 3103 5/28/2012 6:00:11.065 AM 6/4/2012 11:00:40.967 AM +07 05:00:29.902000
I would like the last columns output to be 7 (for the days). I have tried trunc and extract like some other posts mentioned but can't seem to get the syntax right. Any ideas?
我希望最后一列输出为 7（几天）。我试过 trunc 和 extract 像提到的其他一些帖子一样，但似乎无法获得正确的语法。有任何想法吗？
回答by Andomar
Judging from your comment, you're using timestampcolumns, not datetime. You could use extractto retrieve the hour difference, and then trunc(.../24)to get the whole number of days:
从您的评论来看，您使用的是timestamp列，而不是datetime. 您可以使用extract来检索小时差，然后trunc(.../24)获取整个天数：
trunc(extract(hour from max(end_interval_time) - min(begin_interval_time))/24)
Or you could cast the timestampto a date:
或者您可以将timestampa强制转换为date：
trunc(cast(max(end_interval_time) as date) -
cast(min(begin_interval_time) as date))
