33、时间戳处理与跨时区转换-优快云博客

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

时间戳处理与跨时区转换

1. TIMESTAMP 与时区处理

1.1 TIMESTAMP 构造函数与时区

TIMESTAMP 构造函数本身不处理时区。不过，可以使用 FROM_TZ 函数为其添加时区信息。例如：

-- 示例中展示 FROM_TZ 函数的使用
--> T1:  28.03.00 08:00:00,000000000 +03:00
--> T2:  28.03.00 08:00:00,000000000

FROM_TZ 函数的第一个参数是 TIMESTAMP 值，第二个参数是时区信息。

1.2 使用 `AT TIME ZONE` 子句

AT TIME ZONE 关键字可用于动态转换 TIMESTAMP 或 TIMESTAMP WITH TIME ZONE 类型的值到不同时区。示例如下：

select current_timestamp,
       current_timestamp AT TIME ZONE 'Australia/Sydney'
from dual;

输出示例：

--> CURRENT_TIMESTAMP
-->        CURRENT_TIMESTAMPAZTIMEZONE'AUSTRALIA/SYDNEY'
--> 24.01.22 12:32:52,896000000 EUROPE/PRAGUE
-->        24.01.22 22:32:52,896000000 AUSTRALIA/SYDNEY

时间区可以通过名称指定，也可以使用小时:分钟的格式。但 AT TIME ZONE 关键字只能用于 TIMESTAMP 类型，不能用于 DATE 类型。若要对 DATE 类型进行时区转换，需要先将其转换为 TIMESTAMP 类型，可使用 TO_TIMESTAMP 或 CAST 函数。示例如下：

select CAST(DATE '2014-04-08' as TIMESTAMP) d1,
       CAST(DATE '2014-04-08' as TIMESTAMP)
              AT TIME ZONE 'US/Easter' d2,
       CAST(DATE '2014-04-08' as TIMESTAMP)
              AT TIME ZONE '3:00' d3
from dual;

1.3 更复杂的转换示例

下面的代码展示了一个更复杂的转换过程：

select FROM_TZ(CAST(sysdate as TIMESTAMP), '5:00')
              AT TIME ZONE 'Europe/Brussels'
from dual;

处理步骤如下：
1. sysdate 提供 DATE 元素，如 08.03.2022 15:15:17 。
2. 将其转换为 TIMESTAMP 格式，添加秒的小数部分，得到 08.03.22 15:15:17,000000000 。
3. 调用 FROM_TZ 函数添加时区，结果为 08.03.22 15:17:15,000000000 +05:00 。
4. 使用 AT TIME ZONE 子句进行最终转换，输出为 08.03.22 11:17:55,000000000 EUROPE/BRUSSELS 。

2. 使用 `NEW_TIME` 函数转换 DATE 值

2.1 `NEW_TIME` 函数介绍

NEW_TIME 函数用于将 DATE 值从一个时区转换到另一个时区。其语法如下：

NEW_TIME(<p_date>, <input_time_zone>, <output_timezone>)

所有参数都需要指定。如果输入的 DATE 值为 NULL，则输出也为 NULL。

2.2 `NEW_TIME` 函数的限制

示例查询：

select
    TO_DATE('15-12-2015 03:23:45', 'DD-MM-YYYY HH24:MI:SS')
           "Original date and time",
    NEW_TIME(TO_DATE('15-12-2015 03:23:45',
                     'DD-MM-YYYY HH24:MI:SS'),
             'AST', 'PST')
           "New date and time"
from dual;

2.3 替代方案

若要使用非 NEW_TIME 支持的时区，可以使用 FROM_TZ 函数和 AT TIME ZONE 子句。以下是一个自定义函数 CONVERT_DATE_TIMEZONES 的实现：

create or replace function CONVERT_DATE_TIMEZONES
 (p_date DATE,
  input_timezone varchar,
  output_timezone varchar
  )
 return date
  is
    v_timestamp TIMESTAMP WITH TIME ZONE;
begin
    v_timestamp:=FROM_TZ(CAST(p_date AS TIMESTAMP),
                         input_timezone);
    return CAST(v_timestamp AT TIME ZONE output_timezone
                as DATE);
end;
/

3. TIMESTAMP 规范化

3.1 示例展示

以下查询展示了 TIMESTAMP 规范化的过程：

select sysdate "Original value",
       FROM_TZ(CAST (sysdate as TIMESTAMP), '05:00')
              "Value extended by the input time zone",
       CONVERT_DATE_TIMEZONES(sysdate, '05:00', '07:00')
              "Output"
from dual;

输出示例：

--> 10.03.2022 08:38:33
--> 10.03.2022 08:38:33,000000000 +05:00
--> 10.03.2022 10:38:33

3.2 UTC 作为参考时间

3.3 提取 UTC 值

可以使用 SYS_EXTRACT_UTC 函数从指定值中提取 UTC 时间。示例如下：

alter session set time_zone='3:00';
select current_timestamp,
       SYS_EXTRACT_UTC(current_timestamp)
from dual;

输出示例：

--> CURRENT_TIMESTAMP
-->        SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)
--> 08.03.22 16:51:05,593000000 +03:00
-->        08.03.22 13:51:05,593000000

SYS_EXTRACT_UTC 函数不能直接用于 DATE 类型，需要先将其转换为 TIMESTAMP 类型。

3.4 获取时区偏移量

可以使用 TZ_OFFSET 函数获取特定地区的时区偏移量。示例如下：

select TZ_OFFSET('Europe/Paris')
from dual;

输出示例：

--> TZ_OFFSET('EUROPE/PA'IS')
--> +01:00

TZ_OFFSET 函数的语法如下：

select TZ_OFFSET({ '<time_zone_name>'
                  | '{ + | - } <hh> : <mi>'
                  | SESSIONTIMEZONE
                  | DBTIMEZONE
                  }
                )
from dual;

4. 使用 TIMESTAMP 数据类型扩展进行本地值反射

4.1 TIMESTAMP 相关数据类型

有三种 TIMESTAMP 数据类型：
- TIMESTAMP ：不包含时区信息，主要用于向后兼容或不需要引用时区的值。
- TIMESTAMP WITH TIME ZONE ：通过偏移扩展将时区映射到值上，可在不同地区进行时区转换和协调，在数据库中是规范化的。
- TIMESTAMP WITH LOCAL TIME ZONE ：不明确声明时区，而是直接应用转换，根据用户所在时区重新计算并提供小时和分钟元素的原始值。

4.2 示例表创建与操作

创建一个包含这三种数据类型的表：

create table timetab(t1 TIMESTAMP,
                     t2 TIMESTAMP WITH TIME ZONE,
                     t3 TIMESTAMP WITH LOCAL TIME ZONE);
alter session set time_zone='3:00';
insert into timetab
values(TO_TIMESTAMP('11.1.2022 15:24:12.4',
                    'DD.MM.YYYY HH24:MI:SS.FF'),
       TO_TIMESTAMP('11.1.2022 15:24:12.4',
                    'DD.MM.YYYY HH24:MI:SS.FF'),
       TO_TIMESTAMP('11.1.2022 15:24:12.4',
                    'DD.MM.YYYY HH24:MI:SS.FF'));
alter session set time_zone='5:00';
select * from timetab;

输出示例：

--> T1:  11.01.22 15:24:12,400000000
--> T2:  11.01.22 15:24:12,400000000 +03:00
--> T3:  11.01.22 17:24:12,400000000

可以看到， t1 不反映时区变化， t2 明确包含时区信息， t3 根据会话时区直接应用转换。

4.3 多会话时间管理示例

创建一个新表 timetab5 来演示多会话时间管理：

create table timetab5(id integer,
                      t1 TIMESTAMP,
                      t2 TIMESTAMP WITH TIME ZONE,
                      t3 TIMESTAMP WITH LOCAL TIME ZONE);

两个不同时区会话的操作示例如下：
| 会话 1 | 会话 2 |
| — | — |
| alter session set time_zone='3:00';
insert into timetab5 values(1, TO_TIMESTAMP('10.1.2022 15:24:12.4', 'DD.MM.YYYY HH24:MI:SS.FF'), TO_TIMESTAMP('10.1.2022 15:24:12.4', 'DD.MM.YYYY HH24:MI:SS.FF'), TO_TIMESTAMP('10.1.2022 15:24:12.4', 'DD.MM.YYYY HH24:MI:SS.FF'));
commit;
select * from timetab5 where id=1; | alter session set time_zone='5:00';
insert into timetab5 values(2, TO_TIMESTAMP('10.1.2022 15:24:12.4', 'DD.MM.YYYY HH24:MI:SS.FF'), TO_TIMESTAMP('10.1.2022 15:24:12.4', 'DD.MM.YYYY HH24:MI:SS.FF'), TO_TIMESTAMP('10.1.2022 15:24:12.4', 'DD.MM.YYYY HH24:MI:SS.FF'));
commit;
select * from timetab5 where id=2; |

需要注意的是，较旧版本的 SQL Developer 在处理本地时区反射时可能存在错误，SQL 客户端和 SQL Developer Release 20 及更高版本正常工作。

4.4 三种数据类型的特点总结

TIMESTAMP ：
- 该数据类型不包含时区信息，在不同时区的环境下使用时，无法自动完成时区转换。
- 适合用于那些不需要考虑时区差异的场景，例如记录本地事件发生的时间，且这些事件的时间无需在不同时区之间进行比较或同步。
- 若要在多区域使用，使用者需自行管理时区转换，否则易出现数据管理不当的问题。
TIMESTAMP WITH TIME ZONE ：
- 此数据类型将时区信息与时间值关联，通过偏移量扩展来明确表示时区。
- 可以方便地在不同时区之间进行转换和协调，确保数据在数据库中时区信息的一致性和准确性。
- 可通过 AT TIME ZONE 等关键字扩展子句进行时区转换，以满足不同用户在不同时区的使用需求。
TIMESTAMP WITH LOCAL TIME ZONE ：
- 该类型不明确显示时区信息，而是根据会话的时区设置直接对时间值进行转换。
- 能自动根据用户所在的时区提供相应的时间值，无需用户手动干预时区转换。
- 由于不明确显示时区，在某些需要明确知道时区信息的场景下可能不太适用。

4.5 时间戳转换流程总结

下面是一个 mermaid 格式的流程图，展示了时间戳在不同数据类型和时区之间转换的主要流程：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px

    A([开始]):::startend --> B{数据类型}:::decision
    B -->|TIMESTAMP| C(无时区信息):::process
    B -->|TIMESTAMP WITH TIME ZONE| D(含时区偏移):::process
    B -->|TIMESTAMP WITH LOCAL TIME ZONE| E(按会话时区转换):::process
    C --> F{是否需要时区转换}:::decision
    D --> F
    E --> F
    F -->|是| G(使用 AT TIME ZONE 或其他方法转换):::process
    F -->|否| H(直接使用):::process
    G --> I([结束]):::startend
    H --> I

5. 时间戳处理的实际应用与注意事项

5.1 实际应用场景

在线会议与活动安排 ：在全球范围内举办在线会议或活动时，需要确保所有参与者看到的时间是基于其所在时区的正确时间。使用 TIMESTAMP WITH TIME ZONE 或 TIMESTAMP WITH LOCAL TIME ZONE 数据类型可以方便地实现这一点，避免因时区差异导致的时间混乱。
金融交易记录 ：金融交易通常需要精确的时间记录，并且可能涉及不同地区的交易方。使用支持时区处理的时间戳数据类型可以确保交易时间在不同时区之间的一致性和准确性，便于进行交易记录的查询和分析。
日志记录与监控 ：在分布式系统中，不同服务器可能位于不同的时区。使用包含时区信息的时间戳可以准确记录系统事件发生的时间，便于进行系统监控和故障排查。

5.2 注意事项

SQL Developer 版本问题 ：如前文所述，较旧版本的 SQL Developer 在处理本地时区反射时可能存在错误。建议使用 SQL 客户端和 SQL Developer Release 20 及更高版本，以确保时间戳数据的正确显示和处理。
NLS 参数影响 ：时间戳的输出格式受 NLS（National Language Support）参数的影响。在不同的 NLS 设置下，时间戳的显示格式可能会有所不同，例如使用 12 小时制或 24 小时制，以及不同的日期和时间分隔符。在进行时间戳处理和显示时，需要注意 NLS 参数的设置，以确保输出格式符合预期。
夏令时处理 ：夏令时会导致部分地区的时间在特定时间段内发生变化。在进行时间戳处理时，需要考虑夏令时的影响，确保时间转换的准确性。可以通过查询相关的时区数据库或使用支持夏令时处理的函数来实现。

5.3 总结

时间戳处理和时区管理是数据库开发和应用中不可忽视的重要方面。通过合理选择和使用不同的时间戳数据类型，以及掌握相关的时区转换函数和方法，可以有效地解决跨时区数据处理的问题，确保数据的准确性和一致性。在实际应用中，需要根据具体的业务需求和场景选择合适的解决方案，并注意相关的注意事项，以避免因时区问题导致的错误和数据不一致。

希望本文对你理解时间戳处理和时区管理有所帮助，如果你在实际应用中遇到任何问题或有进一步的疑问，欢迎在评论区留言讨论。