如何修改Oracle ADW默认时区及处理ETL存储过程SYSDATE取值问题

Oracle ADW时区调整（UTC转EST）最优解决方案 #

首先明确ADW的底层限制：

Oracle ADW作为完全托管的自治数据库服务，不支持用户修改底层操作系统的时区配置，默认OS时区固定为UTC，因此无法通过修改OS时区让SYSDATE直接返回EST时间，也不建议通过修改数据库级时区参数实现需求，该参数仅影响CURRENT_DATE等会话时区相关函数，对SYSDATE完全无效。

针对存量大量使用SYSDATE的ETL存储过程场景，按改造成本从低到高推荐以下方案：

方案1：批量替换函数调用（优先推荐） #

该方案改造风险最低，适配夏令时规则，适合绝大多数场景：

• 第一步：批量替换所有存储过程中SYSDATE的调用逻辑
  替换目标：将SYSDATE统一替换为CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE)
  • 逻辑说明：SYSTIMESTAMP会自带服务器UTC的时区属性，通过AT TIME ZONE转换为美东时区，再转成DATE类型，输出结果和原生SYSDATE格式完全一致，且自动适配夏令时（避免静态EST时区冬夏令时切换出错）
  • 改造效率：可通过Oracle正则匹配ALL_SOURCE系统视图，批量拉取存储过程源码替换后重新编译，不需要逐行修改业务逻辑，半天内即可完成数千个存储过程的全量改造
• 第二步：新增统一时间取数规范
  封装自定义公共函数供后续新业务调用，后续时区调整只需修改单个函数，不需要调整业务代码，示例：

  CREATE OR REPLACE FUNCTION F_GET_EST_CURR_DATE RETURN DATE 
  AUTHID DEFINER
  AS
  BEGIN
    RETURN CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE);
  END;
  /
  

方案2：自定义同名函数覆盖（极端场景用） #

如果完全不允许修改存量存储过程的源码，可使用该方案实现零代码改造：

• 在ETL业务用户的schema下创建和内置函数同名的SYSDATE自定义函数，Oracle调用函数时会优先使用当前schema下的自定义函数，覆盖内置函数逻辑，示例：

  CREATE OR REPLACE FUNCTION SYSDATE RETURN DATE 
  AUTHID DEFINER
  AS
  BEGIN
    RETURN CAST(SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS DATE);
  END;
  /
  

• 注意事项：该方案会影响当前业务schema下所有调用SYSDATE的逻辑，上线前必须做全量业务回归测试，避免非预期影响。

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