时间戳

首页 > 解决方案 > 难以置信，Oracle 不时返回不同的查询结果

sql - 难以置信，Oracle 不时返回不同的查询结果

问题描述

我有一个 Oracle 查询，奇怪的是查询结果有时会有所不同。

select 
cast(pmm_datetime - 1 / 24 * utc_offset as timestamp) as pmm_datetime_utc,
count(distinct(NODEB_NAME)) as num_of_recs
from pmmcounter_db.WC_B_NODEBFUNCTION_QH t1 
where pmm_datetime >= to_timestamp('201810230525','YYYYMMDDHH24MI')
and cast(pmm_datetime - 1 / 24 * utc_offset as timestamp) = '2018-10-22 19:15:00.000'
group by cast(pmm_datetime - 1 / 24 * utc_offset as timestamp)

大多数时候，查询返回的预期结果如下：

PMM_DATETIME_UTC        NUM_OF_RECS
2018-10-22 19:15:00.000 6930

但是，它随机返回如下结果。如您所见，相同的日期时间和状态被分成几行。

PMM_DATETIME_UTC        NUM_OF_RECS
2018-10-22 19:15:00.000 785
2018-10-22 19:15:00.000 1990
2018-10-22 19:15:00.000 162
2018-10-22 19:15:00.000 3993

更奇怪的是，我尝试用 sum for QH_NUM_OF_RECS 再放一个组，结果是一样的。Oracle 似乎无法按 PMM_DATETIME_UTC 分组？

这里发生了什么？为什么查询结果会不一样？？？？？？

标签： sqloracle

解决方案

不要从TIMESTAMP数据类型中减去数字；这是 Oracle 应用于DATE数据类型的操作，并将隐式TIMESTAMP转换为 aDATE并丢失精度（然后您必须应用显式转换才能将其转换回来）。

相反，减去一个INTERVAL（或用于NUMTODSINTERVAL( utc_offset, 'HOUR' )生成间隔）的倍数：

SELECT pmm_datetime - utc_offset * INTERVAL '1' HOUR as pmm_datetime_utc,
       COUNT( DISTINCT NODEB_NAME ) as num_of_recs
FROM   pmmcounter_db.WC_B_NODEBFUNCTION_QH t1 
WHERE  pmm_datetime >= TIMESTAMP '2018-10-23 05:25:00'
AND    pmm_datetime - utc_offset * INTERVAL '1' HOUR = TIMESTAMP '2018-10-22 19:15:00.000'
GROUP BY pmm_datetime - utc_offset * INTERVAL '1' HOUR

您还可以使用时间戳文字TIMESTAMP '2018-10-22 19:15:00.000'，而不是依赖从字符串到TIMESTAMP数据类型的隐式转换。

您还可以使用数据类型来管理使用函数和TIMESTAMP WITH TIME ZONEUTC 的转换。FROM_TZAT TIME ZONE 'UTC'

为什么[可能]查询结果不同？

数据类型可以TIMESTAMP(6)包含微秒值。如果您只对微秒值感兴趣，则将该值转换为 aTIMESTAMP(3)以降低精度。

您可以使用：

SELECT TO_CHAR(
         pmm_datetime - utc_offset * INTERVAL '1' HOUR,
         'YYYY-MM-DD HH24:MI:SS.FF6'
       ) as pmm_datetime_utc,
       COUNT( DISTINCT NODEB_NAME ) as num_of_recs
...

看看是不是这样。

推荐阅读