计算时间戳差异 - 不超过 48 小时

Question

我正在计算两个时间戳之间的差异，当差异超过 48 小时时，这在某种程度上不起作用。

这是我的代码： select systimestamp, mystoredtimestamp, extract( hour from diff ) || ' hours ' || extract( minute from diff ) || ' minutes' from (select (systimestamp - mystoredtimestamp)) diff from dual)

输出为

1. systimestamp: 30-OCT-17 11.48.43.783572 AM +00:00,
2. mystoredtimestamp: 27-OCT-17 07.30.36.956687 AM,
3. difference: 4 hours 18 minutes

（不知何故，这个降价编号没有呈现，以上是查询的三个不同列）

mystoredtimestamp 采用 TIMESTAMP(6) WITH LOCAL TIME ZONE 数据类型。这就是导致问题的原因吗？如果是这样，我现在该如何解决这个问题？

Answer 1

您

还需要提取天数。 它不会自动转换为小时数。

SELECT
  extract ( DAY FROM diff )
  || ' days '
  ||extract( hour FROM diff )
  || ' hours '
  || extract( minute FROM diff )
  || ' minutes'
FROM
  (
    SELECT
      systimestamp - CAST ( '27-OCT-17 07.30.36.956687 AM' AS TIMESTAMP(6)
      WITH LOCAL TIME ZONE ) diff
    FROM
      DUAL
  );

O/p

3 days 2 hours 26 minutes

如果您想要以小时为单位，请乘以 24 并添加天数。

SELECT
  24 * extract ( DAY FROM diff )
   +
 extract( hour FROM diff )
  || ' hours '
  || extract( minute FROM diff )
  || ' minutes'
FROM
  (
    SELECT
      systimestamp - CAST ( '27-OCT-17 07.30.36.956687 AM' AS TIMESTAMP(6)
      WITH LOCAL TIME ZONE ) diff
    FROM
      DUAL
  );

O/p

74 hours 28 minutes