如何在 Oracle 中找到不同的 b/w TIMESTAMP 格式值?
我有两个 timestamp 列:arrTime 和 depTime.我需要找出公共汽车晚点的分钟数.我尝试了以下方法:
I have two timestamp columns: arrTime and depTime.
I need to find the number of munites the bus is late.
I tried the following:
SELECT RouteDate, round((arrTime-depTime)*1440,2) time_difference
FROM ...
我收到以下错误:inconsistent datatype.预期的数字，但有间隔天到秒
如何解析分钟数?
如果我简单地减去:SELECT RouteDate, arrTime-depTime)*1440 time_difference结果正确但格式不正确:
If i simply subtract: SELECT RouteDate, arrTime-depTime)*1440 time_difference
The result is correct but not well formatted:
time_difference
+00000000 00:01:00 0000000
推荐答案
时间戳算术的结果是一个INTERVAL数据类型.你有一个 INTERVAL DAY TO SECOND...
The result of timestamp arithmetic is an INTERVAL datatype. You have an INTERVAL DAY TO SECOND there...
如果您想要分钟数，一种方法是使用 EXTRACT()，例如:
If you want the number of minutes one way would be to use EXTRACT(), for instance:
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 )
trick"版本之所以有效，是因为 运算符优先顺序 以及日期和时间戳算术之间的差异.
The "trick" version works because of the operator order of precedence and the differences between date and timestamp arithmetic.
最初的操作是这样的:
date + ( interval * number ) - date
如文档中所述:
Oracle 先计算括号内的表达式，然后再计算括号外的表达式.
Oracle evaluates expressions inside parentheses before evaluating those outside.
因此，第一个操作将间隔乘以 1,440.一个区间，即一个离散的时间段，乘以一个数字就是另一个离散的时间段，请参阅有关日期时间和区间算术的文档.所以，这个操作的结果是一个区间，给我们留下:
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:
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
根据文档，这会产生一个表示天数的整数.但是，您将原始时间间隔乘以 1,440，因此这现在代表了原本天数的 1,440 倍.然后就剩下秒数了.
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.
这是值得注意的:
当间隔计算返回日期时间值时，结果必须是实际的日期时间值，否则数据库会返回错误.例如，接下来的两个语句返回错误:
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 will fail, rarely but it will still fail. As ever it's best to do it properly.
相关文章