如何在 Oracle 中找到不同的黑白 TIMESTAMP 格式值?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/17413096/
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
How to find difference b/w TIMESTAMP format values in Oracle?
提问by Buras
I have two timestamp
columns: arrTime
and depTime
.
I need to find the number of munites the bus is late.
I tried the following:
我有两timestamp
列:arrTime
和depTime
。我需要找到公共汽车晚点的数量。我尝试了以下方法:
SELECT RouteDate, round((arrTime-depTime)*1440,2) time_difference
FROM ...
I get the following error: inconsistent datatype . expected number but got interval day to second
我收到以下错误: inconsistent datatype . expected number but got interval day to second
How can i parse the nuber of minutes?
我如何解析分钟数?
If i simply subtract: SELECT RouteDate, arrTime-depTime)*1440 time_difference
The result is correct but not well formatted:
如果我只是减去:SELECT RouteDate, arrTime-depTime)*1440 time_difference
结果正确但格式不正确:
time_difference
+00000000 00:01:00 0000000
回答by Ben
The result of timestamp arithmetic is an INTERVAL datatype. You have an INTERVAL DAY TO SECONDthere...
时间戳算法的结果是 INTERVAL 数据类型。你在那里有一个间隔日......
If you want the number of minutes one way would be to use EXTRACT()
, for instance:
如果您想要分钟数,一种方法是使用EXTRACT()
,例如:
select extract( minute from interval_difference ) * 60
+ extract( hour from interval_difference ) * 60
+ extract( day from interval_difference ) * 60 * 24
from ( select systimestamp - (systimestamp - 1) as interval_difference
from dual )
Alternatively you can use a trick with dates:
或者,您可以使用带有日期的技巧:
select sysdate + (interval_difference * 1440) - sysdate
from (select systimestamp - (systimestamp - 1) as interval_difference
from dual )
The "trick" version works because of the operator order of precedenceand the differences between date and timestamp arithmetic.
“技巧”版本之所以有效,是因为运算符的优先顺序以及日期和时间戳算法之间的差异。
Initially the operation looks like this:
最初的操作如下所示:
date + ( interval * number ) - date
As mentioned in the documentation:
如文档中所述:
Oracle evaluates expressions inside parentheses before evaluating those outside.
Oracle 先计算括号内的表达式,然后再计算括号外的表达式。
So, the first operation performed it to multiply the interval by 1,440. An interval, i.e. a discrete period of time, multiplied by a number is another discrete period of time, see the documentation on datetime and interval arithmetic. So, the result of this operation is an interval, leaving us with:
因此,第一个操作执行它以将间隔乘以 1,440。一个间隔,即一个离散的时间段,乘以一个数字是另一个离散的时间段,请参阅有关日期时间和间隔算术的文档。所以,这个操作的结果是一个区间,给我们留下:
date + interval - date
The plus operator takes precedence over the minus here. The reason for this could be that an interval minus a date is an invalid operation, but the documentation also implies that this is the case (doesn't come out and say it). So, the first operation performed is date + interval. A date plus an interval is a date. Leaving just
此处加号运算符优先于减号。这样做的原因可能是间隔减去日期是无效操作,但文档也暗示了这种情况(没有出来说出来)。因此,执行的第一个操作是日期 + 间隔。一个日期加上一个间隔就是一个日期。只留下
date - date
As per the documentation, this results in an integer representing the number of days. However, you multiplied the original interval by 1,440, so this now represented 1,440 times the amount of days it otherwise would have. You're then left with the number of seconds.
根据文档,这会产生一个表示天数的整数。但是,您将原始间隔乘以 1,440,因此现在代表了 1,440 倍的天数。然后你会得到秒数。
It's worth noting that:
这是值得一提的是:
When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error. For example, the next two statements return errors:
当间隔计算返回日期时间值时,结果必须是实际的日期时间值,否则数据库将返回错误。例如,接下来的两个语句返回错误:
The "trick" method willfail, rarely but it will still fail. As ever it's best to do it properly.
“trick”方法会失败,很少,但它仍然会失败。与以往一样,最好正确地进行操作。
回答by Thomas Erdman
SELECT (arrTime - depTime) * 1440 time_difference
FROM Schedule
WHERE ...
That will get you the time difference in minutes. Of course, you can do any rounding that you might need to to get whole minutes....
这将使您获得以分钟为单位的时差。当然,您可以进行任何可能需要获得整分钟的四舍五入....
回答by drawnonward
Casting to DATE
first returns the difference as a number, at least with the version of Oracle I tried.
转换到DATE
first 以数字形式返回差异,至少在我尝试过的 Oracle 版本中是这样。
round((cast(arrTime as date) - cast(depTime as date))*1440)
You could use TO_CHAR
then convert back to a number. I have never tested the performance compared to EXTRACT
, but the statement works with two dates instead of an interval which fit my needs.
您可以使用TO_CHAR
then 转换回数字。我从未测试过与 相比的性能EXTRACT
,但该语句适用于两个日期而不是适合我需要的间隔。
Seconds:
秒:
(to_char(arrTime,'J')-to_char(depTime,'J'))*86400+(to_char(arrTime,'SSSSS')-to_char(depTime,'SSSSS'))
Minutes:
分钟:
round((to_char(arrTime,'J')-to_char(depTime,'J'))*1440+(to_char(arrTime,'SSSSS')-to_char(depTime,'SSSSS'))/60)
J
is julian day and SSSSS
is seconds in day. Together they give an absolute time in seconds.
J
是儒略日,SSSSS
是一天中的秒数。它们一起给出了以秒为单位的绝对时间。