如何在Oracle中查找不同的黑白TIMESTAMP格式值? [英] How to find difference b/w TIMESTAMP format values in Oracle?

问题描述

我有两个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 . expected number but got interval day to second

我如何解析分钟数?

如果我简单地减去: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数据类型.您在那里有第二天的间隔时间 ...

The result of timestamp arithmetic is an INTERVAL datatype. You have an INTERVAL DAY TO SECOND there...

如果您想要分钟数，一种方法是使用 ，例如:

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 )

技巧"版本有效是因为运算符的优先顺序以及日期和时间戳算法之间的差异.

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.

这篇关于如何在Oracle中查找不同的黑白TIMESTAMP格式值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！