Oracle兼容SQL在PostgreSQL中报时间范围错误求助

原SQL语句 #

INSERT INTO TEMP_USAGE_REPORTING
  (SELECT ABC.EPCCLASS_ID, ABC.EPCCLASS_SEARCH_PAT, ABC.SERIAL, ABC.EVENT_RECORD_DT, ABC.EVENT_RECORD_TM
   FROM
     (SELECT EVENT_EPC.EPCCLASS_ID, EPCCLASS.EPCCLASS_HEADER, EPCCLASS.EPCCLASS_SEARCH_PAT, EVENT_EPC.EVENT_EPC_URI_SN AS SERIAL, EVENT.EVENT_EVENT_DT, EVENT.EVENT_RECORD_DT, EVENT.EVENT_RECORD_TM, EVENT.EVENT_SUB_ID, ROW_NUMBER() OVER (PARTITION BY EVENT_EPC.EVENT_EPC_URI_SN, EPCCLASS.EPCCLASS_SEARCH_PAT
                                                                                                                                                                                                                                             ORDER BY EVENT.EVENT_RECORD_DT) AS RN
      FROM EVENT
      LEFT JOIN EVENT_EPC ON EVENT.EVENT_SUB_ID = EVENT_EPC.EVENT_SUB_ID
      LEFT JOIN EPCCLASS ON EVENT_EPC.EPCCLASS_ID = EPCCLASS.EPCCLASS_ID
      WHERE TO_TIMESTAMP(EVENT.EVENT_RECORD_DT::TEXT || ' ' || LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 'yyyy-mm-dd HH24MISSFFF') >= TO_TIMESTAMP('2024-02-12'::varchar || ' ' || '170000892'::varchar, 'yyyy-mm-dd HH24MISSFFF')
        AND TO_TIMESTAMP(EVENT.EVENT_RECORD_DT::TEXT || ' ' || LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 'yyyy-mm-dd HH24MISSFFF') <= TO_TIMESTAMP('2024-03-01'::varchar || ' ' || '005959999'::varchar, 'yyyy-mm-dd HH24MISSFFF') ) ABC
   WHERE ABC.RN = 1
   GROUP BY ABC.EPCCLASS_ID, ABC.EPCCLASS_SEARCH_PAT, ABC.SERIAL, ABC.EVENT_RECORD_DT, ABC.EVENT_RECORD_TM);

报错信息 #

ERROR: date/time field value out of range: "2024-02-12 170000892"
SQL state: 22008

该语句在Oracle中可正常运行，但PostgreSQL触发上述错误。时间格式为"HHmmssSSS"，尝试将格式符改为HH24MISSFF3后问题仍未解决。

问题分析与解决方案 #

核心问题 #

PostgreSQL的TO_TIMESTAMP函数对时间部分的解析逻辑和Oracle存在差异：

• Oracle中HH24MISSFFF可以直接解析170000892（对应17:00:00.892）
• PostgreSQL中，HH24MISSFF3要求毫秒部分必须以小数点分隔，而你当前拼接后的字符串是2024-02-12 170000892，PostgreSQL会把170000892当成整体的时分秒数值，直接超出时间范围上限，导致报错。

修复方案 #

需要将时间字符串调整为PostgreSQL可识别的带小数点的毫秒格式，具体修改如下：

修改后的SQL示例

INSERT INTO TEMP_USAGE_REPORTING
  (SELECT ABC.EPCCLASS_ID, ABC.EPCCLASS_SEARCH_PAT, ABC.SERIAL, ABC.EVENT_RECORD_DT, ABC.EVENT_RECORD_TM
   FROM
     (SELECT EVENT_EPC.EPCCLASS_ID, EPCCLASS.EPCCLASS_HEADER, EPCCLASS.EPCCLASS_SEARCH_PAT, EVENT_EPC.EVENT_EPC_URI_SN AS SERIAL, EVENT.EVENT_EVENT_DT, EVENT.EVENT_RECORD_DT, EVENT.EVENT_RECORD_TM, EVENT.EVENT_SUB_ID, ROW_NUMBER() OVER (PARTITION BY EVENT_EPC.EVENT_EPC_URI_SN, EPCCLASS.EPCCLASS_SEARCH_PAT
                                                                                                                                                                                                                                             ORDER BY EVENT.EVENT_RECORD_DT) AS RN
      FROM EVENT
      LEFT JOIN EVENT_EPC ON EVENT.EVENT_SUB_ID = EVENT_EPC.EVENT_SUB_ID
      LEFT JOIN EPCCLASS ON EVENT_EPC.EPCCLASS_ID = EPCCLASS.EPCCLASS_ID
      WHERE TO_TIMESTAMP(
              EVENT.EVENT_RECORD_DT::TEXT || ' ' || 
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 1, 2) || ':' ||
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 3, 2) || ':' ||
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 5, 2) || '.' ||
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 7, 3),
              'yyyy-mm-dd HH24:MI:SS.FF3'
            ) >= TO_TIMESTAMP('2024-02-12 17:00:00.892', 'yyyy-mm-dd HH24:MI:SS.FF3')
        AND TO_TIMESTAMP(
              EVENT.EVENT_RECORD_DT::TEXT || ' ' || 
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 1, 2) || ':' ||
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 3, 2) || ':' ||
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 5, 2) || '.' ||
              SUBSTRING(LPAD(EVENT.EVENT_RECORD_TM::TEXT, 9, '0'), 7, 3),
              'yyyy-mm-dd HH24:MI:SS.FF3'
            ) <= TO_TIMESTAMP('2024-03-01 00:59:59.999', 'yyyy-mm-dd HH24:MI:SS.FF3') ) ABC
   WHERE ABC.RN = 1
   GROUP BY ABC.EPCCLASS_ID, ABC.EPCCLASS_SEARCH_PAT, ABC.SERIAL, ABC.EVENT_RECORD_DT, ABC.EVENT_RECORD_TM);

简化写法（若EVENT_RECORD_TM为数字类型）

如果EVENT_RECORD_TM是数字类型，可通过字符串格式化简化拼接逻辑：

-- 将数字170000892格式化为'17:00:00.892'
TO_CHAR(EVENT.EVENT_RECORD_TM, 'FM000000009') AS FORMATTED_TM
-- 再拼接为标准日期时间字符串
EVENT.EVENT_RECORD_DT::TEXT || ' ' || SUBSTRING(FORMATTED_TM,1,2) || ':' || SUBSTRING(FORMATTED_TM,3,2) || ':' || SUBSTRING(FORMATTED_TM,5,2) || '.' || SUBSTRING(FORMATTED_TM,7,3)

额外优化建议 #

• 避免在WHERE子句中对列进行函数转换（如TO_TIMESTAMP），这会导致索引失效，降低查询性能。建议提前将EVENT_RECORD_DT和EVENT_RECORD_TM合并为TIMESTAMP类型的列并建立索引。
• 原SQL中GROUP BY子句与SELECT列完全一致，且已通过RN=1过滤重复行，GROUP BY可直接移除，减少不必要的计算开销。

内容的提问来源于stack exchange，提问作者Aditya Ranjan