Oracle SQL：将时间戳转换为UTC

Question

Oracle SQL：将时间戳转换为UTC

18

我有一个简单的选择查询，如下所示，但我注意到我得到了地区时间。如何在我的选择语句中将其转换为UTC？

select myTimeStamp, MyName, MyBranch from tableA

结果：'2014年3月27日15:15:26'，'约翰'，'伦敦'

我尝试使用sys_extract_utc(myTimeStamp)，但出现错误：

SQL命令未正确结束。

myTimestamp列的数据类型为'date'。

- Fearghal

1

如果您的列是 date 类型，那么如何知道它具有“区域”时间，并且 Oracle 如何知道它代表哪个区域/偏移量 - 从而如何将其转换为 UTC？您是否有其他信息来确定每个值应该在哪个区域？ - Alex Poole

4个回答

10

根据类型，关于Oracle转换的时区有一些需要注意的地方，具体取决于myTimestamp的数据类型。

带有时区的时间戳

它只是工作。 a_horse_with_no_name 在这里给出了正确的答案。

带本地时区的时间戳

它会被隐式转换为带有时区的时间戳，然后就可以正��工作了。同样，a_horse_with_no_name 在这里也是正确的。

时间戳

虽然它也会被隐式转换为带有时区的时间戳，但默认分配的时区是会话时区（而不是数据库时区）。

显示调用此功能的方法是myTimestamp at local。
或者更好的方式是像Madhawas所说的那样使用from_tz函数来显式构建一个具有与会话不同的明确时区的值。

日期

尝试对日期执行上述任何操作都将失败，如您所描述：

myTimestamp at time zone 'UTC'
ORA-30084：具有时区修饰符的日期时间主键的��据类型无效。

from_tz(myTimestamp， 'America/New_York')
ORA-00932: 不一致的数据类型：预期TIMESTAMP但得到了DATE

这里的解决方案是先将日期转换为时间戳：
select from_tz(cast(myTimestamp as timestamp), 'America/New_York') from tableA
样本脚本

下面的脚本展示了行为。请注意，在我的系统上，dbtimezone是US/Central，而sessiontimezone是GMT-05:00。

我还使用to_char转换输出，因为我发现一些工具可能会以微妙的方式更改结果时间戳，特别是如果它们没有良好的时间戳支持（虽然这在现在很少见，但仍有潜在问题）。
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' / alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS' / alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS TZR' / select dbtimezone ,sessiontimezone ,to_char(timestamp '2017-01-01 06:00:00') as ts ,to_char(timestamp '2017-01-01 06:00:00' at local) as ts_at_local ,to_char(timestamp '2017-01-01 06:00:00' at time zone dbtimezone) as ts_at_db ,to_char(timestamp '2017-01-01 06:00:00' at time zone sessiontimezone) as ts_at_session from dual /
我的系统输出如下（为了易读性，重新格式化为列格式）：
DBTIMEZONE US/Central SESSIONTIMEZONE -05:00 TS 2017-01-01 06:00:00 TS_AT_LOCAL 2017-01-01 06:00:00 -05:00 TS_AT_DB 2017-01-01 05:00:00 US/CENTRAL TS_AT_SESSION 2017-01-01 06:00:00 -05:00

- Chris R. Donnelly

我不知道为什么我的日期类型中包含时间，但是我的下一个问题是...将日期强制转换为dateTime会覆盖变量的时间部分，将其重置为00:00:00吗？ - Fearghal

1

@Fearghal - Oracle的date类型具有时间组件，因此这是完全正常的。转换为timestamp将保留该时间。（小数秒部分将为零）。这很容易测试：select cast(sysdate as timestamp) from dual。 - Alex Poole

哦，我看到了一个问题，我觉得我错过了描述这个问题。上面描述的解决方案将把日期与时区绑定在一起。现在我需要做另一件事：将时间更改为UTC？例如，如果时间是PDT（UTC-7小时）的凌晨1点，我希望这个时间是UTC的上午8点。 - Fearghal

我知道这已经很老了，但是@cdonnellytx，你有以下内容的文档参考吗？虽然它也被隐式转换为带有时区的时间戳，但默认情况下分配的时区是会话时区（而不是数据库时区）。我找不到文件说“会话”时区被使用（这不是我在测试中看到的，我看到的是使用UTC）。 - DDoomUs

@DDoomUs 我没有文档参考，但我添加了一个脚本来复现这种行为。请注意，在我的系统上，dbtimezone是美国中部，而sessiontimezone是-05:00。此外，如果您返回实际值，您的客户端可能会看到不同的行为。较旧版本的Toad会更改输出日期（12.x不会）。 - Chris R. Donnelly

1

@ChrisR.Donnelly，感谢您的回复。使用SQL开发人员，无论是dbtimezone还是sessiontimezone，我都可以看到时区数据的完全相同的值。即使转储列数据也会产生完全独立于时区的日期时间组件数据。我发现当我从实际客户端检索数据时，我得到的是相同的东西，即独立于时区的数据。（只是提供给您和未来可能遇到此问题的任何人的信息） - DDoomUs

5

你需要知道你所在的时区；

SELECT myTimeStamp, from_tz(myTimeStamp, 'America/New_York') AT TIME ZONE 'UTC' utc FROM dual;

- Madhawas

1

在 from_tz 调用中，你需要使用 cast(myTimeStamp as timestamp)，因为它是一个 date 类型。 - Alex Poole

上面描述的解决方案将日期与时区绑定在一起。现在我还需要做一件事情：将时间更改为UTC。例如，如果时间是PDT（UTC-7小时）的凌晨1点，我希望这个时间是UTC上午8点。 - Fearghal

@Fearghal - 这是调整时间。from_tz()将日期与时区EST/EDT（本例中）绑定；'at time zone 'UTC'

将时间转换为UTC。只要您知道日期最初来自哪个时区，这就是您所需的。这对于评论来说是太多代码了，但是：

select from_tz(cast(to_date('2014-03-27 01:00', 'YYYY-MM-DD HH24:MI') as timestamp), 'America/Los_Angeles') at time zone 'UTC' from dual会给出27-MAR-14 08.00.00.000000 UTC`。如果这不是您想要的，请详细说明您的问题。 - Alex Poole

0

从Oracle 19c开始，引入了一个新的函数TO_UTC_TIMESTAMP_TZ

SQL函数TO_UTC_TIMESTAMP_TZ将ISO 8601日期格式字符串作为varchar输入，并返回SQL数据类型TIMESTAMP WITH TIMEZONE的实例。它将输入规范化为协调世界时（以前是格林威治标准时间）的UTC时间。与SQL函数TO_TIMESTAMP_TZ不同，新函数假定输入字符串使用ISO 8601日期格式，并将时区默认为UTC 0。

select TO_UTC_TIMESTAMP_TZ ( to_char(sysdate,'yyyy-mm-dd"T"HH:MI:SS') )  as utc 
   from dual;

 UTC
31-MAR-19 05.45.36.000000 AM +00:00

- Kaushik Nayak

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user330315 · Accepted Answer

select cast(mytimestamp as timestamp) at time zone 'UTC', 
       MyName, 
       MyBranch 
from tableA

因为mytimestamp实际上是一个date而不是时间戳，所以你需要进行强制转换。这样做可以让Oracle假设存储在mytimestamp中的信息处于服务器的时区-如果不是这种情况，则需要使用Madhawas的解决方案。