34、全球业务拓展中的时区管理与Oracle云解决方案

redis7keeper

于 2025-12-16 01:08:30 发布

阅读量3

点赞数

CC 4.0 BY-SA版权

分类专栏：驾驭时间：Oracle云时序之道文章标签：时区管理 Oracle云 SQL翻译配置文件

本文链接：https://blog.youkuaiyun.com/redis7keeper/article/details/155956547

驾驭时间：Oracle云时序之道专栏收录该内容

34 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

全球业务拓展中的时区管理与Oracle云解决方案

1. 本地与全球业务拓展中的时区问题

在业务发展过程中，时区管理是一个不可忽视的问题。以销售管理为例，一家小镇商店最初只有一个收银台，电脑作为中央数据存储库。随着业务繁荣，销售增加，商店需要扩展，增加了收银台数量，本地电脑已无法满足需求，于是店主在办公室设置了服务器。之后，业务进一步发展，在城市乃至全国开设了更多店铺，数据迁移到由外部公司运营的本地服务器机房，但这种解决方案成本高且可靠性低。

几年后，公司业务拓展到全球，本地服务器机房因安全、健壮性和抗性等原因不再适用，于是采用了云账户，此时报表、统计和分析模块运行良好。然而，当业务扩展到全球时，分析方面出现了一个重要问题：如何获取上一小时的销售信息？

例如，以下代码尝试获取上一小时创建的订单：

select *
 from orders
 where order_date > sysdate – 1/24;

但由于不同城市有不同的时区，上一小时的时间应该与本地时间绑定，而不是服务器（云端）数据库的时间。因此，需要修改上述查询以获得有效输出。

解决方案有多种，原则上需要引用客户端的时间值，可通过获取 current_date （或 current_timestamp ），或者将服务器（数据库）的日期和时间值进行转换，并应用时区偏移到客户端的视角。

2. 时区管理相关问题解答

以下是一些时区管理相关的问题及答案：
|问题|选项|答案|
|----|----|----|
|如何获取客户端时区？可以使用哪个函数？|A. The SESSIONTIMEZONE function of the STANDARD package
B. The MYTIMEZONE function of the STANDARD package
C. The UTC_SHIFT function of the STANDARD package
D. The DBTIMEZONE function of the STANDARD package|A|
|选择指定数据库时区的最佳选项：|A. TZH only
B. TZH:TZM only
C. Name representation only
D. TZH:TZM and name representation|D|
|以下 select 语句的列使用了什么数据类型？
select FROM_TZ(cast(sysdate as TIMESTAMP), '5:00')
AT TIME ZONE 'Europe/Brussels'
from dual; |A. DATE
B. TIMESTAMP
C. TIMESTAMP WITH TIME ZONE
D. TIMESTAMP WITH LOCAL TIME ZONE|C|
|哪个函数接受三个参数 - 输入时间戳值以及要计算的原始和目标时区 - 并为输入值应用时区偏移？|A. FROM_TZ
B. NEW_TIME
C. TIMESTAMP
D. AT TIME ZONE|B|
|哪个函数从 TIMESTAMP 值中获取UTC标准化值？|A. UTC
B. FROM_TZ
C. NEW_UTC_TIME
D. SYS_EXTRACT_UTC|D|

3. Oracle云环境下的时区反射

在过去，系统管理员处理整体系统复杂性和性能问题，服务器负责日期和时间反射，无论会话定义如何，都能确保正确管理。因此， DATE 和 TIMESTAMP 值分别通过 sysdate 和 systimestamp 函数获取。但随着全球化的发展，情况发生了变化，服务器时区无法管理数据，因为需要考虑不同地区和相关时区，所以应使用会话视角反射。

当客户将数据库迁移到云环境时，时间管理变得困难，因为没有合适的时间点参考。仅让服务器使用UTC作为参考是不够的，必须正确设置和评估时区。云环境中的专用虚拟机只能部分解决问题，因为虽然可以根据地区直接设置时区，但这需要开发人员或管理员管理资源，消耗大量时间和资源。

在Oracle云环境中，需要考虑以下几个方面：
- 客户端和服务器日期时间值总结 ：云管理的数据库服务通常在数据库中引用UTC时区，这会导致两个结果：
- sysdate 和 systimestamp 函数的输出始终与UTC相关，无论数据库时区设置如何；而 current_date 和 current_timestamp 则遵循数据库时区。
- 历史上，应用程序引用 sysdate 和 systimestamp 而非 current_date 和 current_timestamp ，主要是为了确保输出可靠性。但在迁移到云并跨区域扩展系统时，应主要使用会话引用，即 sysdate 应替换为 current_date ， current_timestamp 应替换为 systimestamp 。
- 实现翻译配置文件 ：可以使用SQL翻译配置文件进行动态SQL转换。以下是具体操作步骤：
1. 创建翻译配置文件：

begin
 -- create profile
 DBMS_SQL_TRANSLATOR.CREATE_PROFILE ('DATE_PROF');
 -- register transformation for the DATE value
 DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION
 (profile_name => 'DATE_PROF',
  sql_text=> 'select sysdate from dual',
  translated_text=>'select current_date from dual');
 -- register transformation for the TIMESTAMP value
 DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION
 (profile_name => 'DATE_PROF',
  sql_text=> 'select systimestamp from dual',
  translated_text=>'select current_timestamp from dual');
end;
/

2. 启用配置文件：

alter session set SQL_TRANSLATION_PROFILE=DATE_PROF;

但这种方法存在局限性，需要明确列出每个语句，可能需要用户付出大量努力，且容易出错。
- 使用 SYSDATE_AT_DBTIMEZONE 参数 ：该参数简化了活动，只需正确设置参数，即可将非UTC数据库迁移到云环境，变得简单、可靠且高效。

4. 开发SQL代码翻译包

可以应用SQL翻译配置文件的变体，开发一个特定的包来实现动态查询翻译。具体步骤如下：
1. 删除现有配置文件并创建包规范：

exec DBMS_SQL_TRANSLATOR.DROP_PROFILE ('DATE_PROF');
create or replace package DATE_TRANSLATOR_PACKAGE is
 procedure TRANSLATE_SQL(sql_text IN clob,
                         translated_text OUT clob);
 procedure TRANSLATE_ERROR(error_code IN binary_integer,
                           translated_code OUT binary_integer,
                           translated_sql_state OUT varchar);
end;
/

创建包体，使用正则表达式替换函数调用：

create or replace package body DATE_TRANSLATOR_PACKAGE
 is
 procedure TRANSLATE_SQL(sql_text in clob,
                         translated_text out clob)
 is
 begin
  translated_text:=REGEXP_REPLACE(sql_text,
                                  'sysdate',
                                  'current_date',
                                  1,0,'i');
  translated_text:=REGEXP_REPLACE(translated_text,
                                  'systimestamp',
                                  'current_timestamp',
                                  1,0,'i');
 end;
 procedure TRANSLATE_ERROR(error_code IN binary_integer,
                           translated_code OUT binary_integer,
                           translated_sql_state OUT varchar)
 is
 begin
  null;
 end;
end;
/

注册包为SQL翻译配置文件的源：

-- 创建配置文件
dbms_SQL_TRANSLATOR.CREATE_PROFILE
 (profile_name => 'DATE_PROF');
-- 设置属性
DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE
 (profile_name => 'DATE_PROF',
  attribute_name
  => dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX,
  attribute_value
  => dbms_sql_translator.ATTR_VALUE_FALSE);
-- 注册配置文件到包
DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE
 (profile_name => 'DATE_PROF',
  attribute_name
  => dbms_sql_translator.attr_translator,
  attribute_value => 'DATE_TRANSLATOR');

通过以上方法，可以在不重写代码的情况下，自动实现客户端和服务器时区之间的转换，解决业务全球拓展中的时区管理问题。

graph LR
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;

    A(业务本地运营):::process --> B(业务扩展到城市):::process
    B --> C(业务扩展到全国):::process
    C --> D(业务扩展到全球):::process
    D --> E(出现时区管理问题):::process
    E --> F(使用SQL翻译配置文件):::process
    E --> G(使用SYSDATE_AT_DBTIMEZONE参数):::process
    F --> H(创建包进行动态翻译):::process

综上所述，在全球业务拓展中，时区管理至关重要。通过合理运用上述方法，可以有效解决时区相关问题，确保业务在不同地区的顺利运行。

全球业务拓展中的时区管理与Oracle云解决方案

5. SQL翻译配置文件的局限性与改进思路

虽然SQL翻译配置文件为动态SQL转换提供了一种解决方案，但它存在明显的局限性。在使用SQL翻译配置文件时，需要明确列出每个要转换的语句，这不仅需要用户付出大量的精力，还存在较高的出错风险。用户必须考虑到所有可能引用 sysdate 和 systimestamp 函数的查询，并将它们一一列在翻译配置文件中。例如，在一个复杂的业务系统中，可能存在成百上千个不同的查询语句使用了这些函数，要将它们全部准确地列出并进行转换是一项艰巨的任务。

为了改进这种情况，我们可以采用开发特定包的方式。通过创建一个包含 TRANSLATE_SQL 和 TRANSLATE_ERROR 过程的包，利用正则表达式对查询语句中的函数调用进行替换，能够实现更灵活、更全面的动态查询翻译。这种方法避免了手动列出每个语句的繁琐过程，减少了出错的可能性。

6. 时区管理在实际业务中的重要性

时区管理在全球业务拓展中具有至关重要的意义。以在线会议和销售统计为例，如果不进行正确的时区管理，可能会导致严重的问题。

在在线会议方面，如果没有考虑时区差异，可能会出现参会者在会议结束后才到达的情况。例如，一个总部位于纽约的公司与位于东京的合作伙伴进行视频会议，若没有正确设置时区，东京的参会者可能会按照纽约时间来安排，从而错过会议。

在销售统计方面，准确的时区管理能够确保获取到正确的销售数据。如前面提到的获取上一小时销售信息的例子，如果不考虑时区差异，使用服务器的时间进行统计，可能会得到错误的结果。不同地区的营业时间不同，销售高峰也会有所差异，只有结合本地时区进行统计，才能得到有价值的销售数据，为业务决策提供支持。

7. 时区管理操作步骤总结

为了更好地管理时区，以下是一些关键的操作步骤总结：
|操作类型|操作步骤|代码示例|
| ---- | ---- | ---- |
|设置数据库时区|使用 alter database set time_zone 语句设置数据库时区，设置后可能需要重启实例使设置生效| alter database set time_zone = '+09:00';
select DBTIMEZONE from dual; |
|设置客户端时区|使用 alter session set time_zone 语句设置客户端时区，可以使用明确的小时偏移或时区区域命名约定| alter session set time_zone='Europe/Brussels';
alter session set time_zone='00:00'; |
|评估服务器和客户端时间差异|使用 select sysdate, current_date from dual 和 select systimestamp, current_timestamp from dual 语句评估差异| select sysdate, current_date from dual;
select systimestamp, current_timestamp from dual; |
|创建SQL翻译配置文件|使用 DBMS_SQL_TRANSLATOR.CREATE_PROFILE 创建配置文件，使用 REGISTER_SQL_TRANSLATION 注册转换语句，最后使用 alter session set SQL_TRANSLATION_PROFILE 启用配置文件| sql begin -- create profile DBMS_SQL_TRANSLATOR.CREATE_PROFILE ('DATE_PROF'); -- register transformation for the DATE value DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION (profile_name => 'DATE_PROF', sql_text=> 'select sysdate from dual', translated_text=>'select current_date from dual'); -- register transformation for the TIMESTAMP value DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION (profile_name => 'DATE_PROF', sql_text=> 'select systimestamp from dual', translated_text=>'select current_timestamp from dual'); end; / alter session set SQL_TRANSLATION_PROFILE=DATE_PROF; |
|开发SQL代码翻译包|删除现有配置文件，创建包规范和包体，使用正则表达式替换函数调用，最后注册包为SQL翻译配置文件的源| sql exec DBMS_SQL_TRANSLATOR.DROP_PROFILE ('DATE_PROF'); create or replace package DATE_TRANSLATOR_PACKAGE is procedure TRANSLATE_SQL(sql_text IN clob, translated_text OUT clob); procedure TRANSLATE_ERROR(error_code IN binary_integer, translated_code OUT binary_integer, translated_sql_state OUT varchar); end; / create or replace package body DATE_TRANSLATOR_PACKAGE is procedure TRANSLATE_SQL(sql_text in clob, translated_text out clob) is begin translated_text:=REGEXP_REPLACE(sql_text, 'sysdate', 'current_date', 1,0,'i'); translated_text:=REGEXP_REPLACE(translated_text, 'systimestamp', 'current_timestamp', 1,0,'i'); end; procedure TRANSLATE_ERROR(error_code IN binary_integer, translated_code OUT binary_integer, translated_sql_state OUT varchar) is begin null; end; end; / dbms_SQL_TRANSLATOR.CREATE_PROFILE (profile_name => 'DATE_PROF'); DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE (profile_name => 'DATE_PROF', attribute_name => dbms_sql_translator.ATTR_FOREIGN_SQL_SYNTAX, attribute_value => dbms_sql_translator.ATTR_VALUE_FALSE); DBMS_SQL_TRANSLATOR.SET_ATTRIBUTE (profile_name => 'DATE_PROF', attribute_name => dbms_sql_translator.attr_translator, attribute_value => 'DATE_TRANSLATOR'); |

8. 总结与展望

在全球业务拓展的背景下，时区管理是一个不可忽视的关键因素。通过对时区管理相关知识的学习和实践，我们了解到了不同的解决方案，如SQL翻译配置文件和开发特定的翻译包，以及如何利用 SYSDATE_AT_DBTIMEZONE 参数简化迁移过程。

在实际应用中，我们需要根据业务的具体需求和系统的复杂程度选择合适的方法。对于一些简单的系统，可能使用SQL翻译配置文件就能够满足需求；而对于复杂的业务系统，开发特定的翻译包可能是更好的选择。

未来，随着全球业务的不断发展，时区管理可能会面临更多的挑战和机遇。例如，随着云计算和大数据技术的不断进步，如何更高效、更准确地处理跨时区的数据将成为一个重要的研究方向。同时，我们也期待Oracle等数据库厂商能够提供更多更便捷的时区管理工具和功能，帮助企业更好地应对全球业务拓展中的时区问题。

graph LR
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;

    A(明确业务需求):::process --> B(选择时区管理方法):::process
    B --> C{简单系统}:::process
    C -->|是| D(使用SQL翻译配置文件):::process
    C -->|否| E(开发特定翻译包):::process
    D --> F(实施与测试):::process
    E --> F
    F --> G(优化调整):::process
    G --> H(持续监控):::process

总之，正确的时区管理能够确保业务在全球范围内的顺利运行，提高企业的运营效率和决策的准确性。我们需要不断学习和探索，以适应不断变化的业务环境。