java - 为什么 Oracle Pivot 产生不存在的结果？

Question

我管理一个数据库，其中包含从各个站点收集的大量气候数据。这是一个 Oracle 12.2 数据库，以下是相关表的概要:

FACT = 特定时间的单独测量

• UTC_START = 测量开始的 UTC 时间
• LST_START = 测量开始的本地标准时间(针对特定站点)
• SERIES_ID = 测量所属系列的 ID(FK 到 SERIES)
• STATION_ID = 进行测量的站点 ID(FK 到 STATION)
• VALUE = 测量值

请注意，每个站点的 UTC_START 和 LST_START 始终具有恒定的差异(LST 与 UTC 的偏移量)。我已确认 UTC_START 和 LST_START 之间的差异不存在超出预期的情况。

SERIES = 系列数据的描述性数据

• SERIES_ID = 系列 ID (PK)
• NAME = 系列的文本名称(例如温度)

STATION = 站点的描述性数据

• STATION_ID = 电台 ID (PK)
• SITE_ID = 站点所在站点的 ID(大多数站点有一个站点，但少数站点有 2 个)
• SITE_RANK = 站点内电台的排名(如果电台超过 1 个)。
• EXT_ID = 网站的外部 ID(提供给我们)

站点的 EXT_ID 适用于该站点的所有站点(但可能不会填充，除非 SITE_RANK == 1，这并不理想，我知道，但不是这里的问题)，并且优先考虑来自排名较低的站点的数据。为了将这些数据组织成可使用的格式，我们使用 PIVOT 将同一站点/时间发生的测量数据收集到行中。

这是查询:

WITH
    primaries AS (
        SELECT site_id, ext_id
        FROM station
        WHERE site_rank = 1
    ),

    data as (
        SELECT d.site_id, d.utc_start, d.lst_start, s.name, d.value FROM (
            SELECT s.site_id, f.utc_start, f.lst_start, f.series_id, f.value,
                 ROW_NUMBER() over (PARTITION BY s.site_id, f.utc_start, f.series_id ORDER BY s.site_rank) as ORDINAL
                 FROM fact f
                      JOIN station s on f.station_id = s.station_id
        ) d
            JOIN series s ON d.series_id = s.series_id
            WHERE d.ordinal = 1
                AND d.site_id = ?
                AND d.utc_start >= ?
                AND d.utc_start < ?
    )

    records as (

        SELECT * FROM data
        PIVOT (
               MAX(VALUE) AS VALUE
               FOR NAME IN (
                   -- these are a few series that we would want to collect by UTC_START
                   't5' as t5,
                   'p5' as p5,
                   'solrad' as solrad,
                   'str' as str,
                   'stc_05' as stc_05,
                   'rh' as rh,
                   'smv005_05' as smv005_05,
                   'st005_05' as st005_05,
                   'wind' as wind,
                   'wet1' as wet1
                   )
                )
    )

SELECT r.*, p.ext_id
FROM records r JOIN primaries p on r.site_id = p.site_id

这就是事情变得奇怪的地方。此查询在 SQLAlchemy、IntelliJ(使用 OJDBC Thin)和 Orcale SQL Developer 中完美运行。但是，当它在我们的 Java 程序中运行时(相同的 JDBC URL 和凭据，使用普通的旧式 JDBC 语句和结果集)，它会给出没有意义的结果。特别是对于同一个站点，它将返回具有相同 UTC_START 但不同 LST_START 的 2 行(回想一下，我已经验证过这一 100% 不会出现在 FACT 表中的任何位置)。为了确保没有发生奇怪的参数处理，我们测试了占位符的硬编码值，并在不同客户端之间复制粘贴完全相同的查询，而返回这些奇怪结果的唯一程序是 Java 程序(它使用与 IntelliJ 完全相同的 OJDBC jar)。

如果有人有任何见解或可能的原因，我们将不胜感激。我们现在有点不知所措。

Answer 1

事实证明Nathan's comment是正确的。虽然这似乎违反直觉(至少对我来说)，但似乎在 DATE 列上调用 ResultSet.getString 实际上会首先转换为时间戳。不幸的是，除非您明确指定，否则时间戳具有使用系统默认时区的默认行为。

这种默认行为意味着在我们不希望的情况下考虑了夏令时，从而导致了所描述的奇怪行为。