如何从Oracle获取时区ID而不是时区偏移量

Question

有没有一种方法可以从Oracle获取时区ID而不是时区偏移量？ 当我执行 SELECT SYSTIMESTAMP AS TIMEZONE FROM DUAL; 时，返回的结果是 13-JAN-21 10.19.52.936031000 AM +05:30。

使用 rs.getObject("TIMEZONE", ZonedDateTime.class) 在Java中检索时区信息时， ZonedDateTime对象的offset和zoneID都设置为+05:30。

然而，我期望看到的是，SELECT SYSTIMESTAMP AS TIMEZONE FROM DUAL; 应该返回 04-JAN-21 02.40.50.000000000 PM ASIA/COLOMBO，并且查询的 Java ZonedDateTime 应该将 offset 设置为 +05:30，将zoneID设置为Asia/Colombo。

是否有办法在数据库级别或Java级别上设置这个？当前的DBTIMEZONE设置为+05:30。

- Klaus

---

请看这个链接：https://dev59.com/v4nca4cB1Zd3GeqP6BdV#29272926 - Wernfried Domscheit

你需要正确设置Java时区，例如使用环境变量JAVA_TOOL_OPTIONS=-Duser.timezone=Asia/Colombo。 - Wernfried Domscheit

Answer 1

如果数据已经在Oracle SQL表中，并且您必须转换为带有时区的时间戳（例如，在您创建的同一表中的新列中），则无需显式地去操作系统，或使用Java或任何其他东西，除了Oracle数据库本身。

从您的问题中不清楚是否必须假定“日期”是在服务器时区（您提到“数据库”，通常意味着服务器）还是客户端时区（您提到“会话”，这意味着客户端）。无论哪种情况：

update <your_table>
set <timestamp_with_time_zone_col> = 
            from_tz(cast<date_col> as timestamp, dbtimezone)
;

如果您需要，可以使用sessiontimezone作为第二个参数进行设置。

这假定数据库（和/或会话）时区已经在数据库中正确设置，客户端也是如此。如果没有正确设置，那么首先需要解决这个问题。如果参数在一开始就被正确设置，Oracle完全能够处理日光节约时间。 （如果它们没有被正确设置，那么您为什么要尝试使您的操作比数据库支持的“更正确”呢？）

例如：在下面的WITH子句中，我模拟了一个具有日期数据类型dt列的表。然后，我将其转换为带有时区的时间戳，以我的会话（客户端）时区为准。

with
  my_table ( dt ) as ( 
    select to_date('2018-06-20 14:30:00', 'yyyy-mm-dd hh24:mi:ss') from dual 
  )
select dt,
       from_tz(cast(dt as timestamp), sessiontimezone) as ts_with_tz
from   my_table
;

DT                  TS_WITH_TZ                                       
------------------- -------------------------------------------------
2018-06-20 14:30:00 2018-06-20 14:30:00.000000000 AMERICA/LOS_ANGELES

参考此链接 StackOverflow 参考链接

这个链接可以帮助你了解如何从Oracle数据库中获取操作系统的时区并以字符串格式表示。

- Rk Developer

Answer 2

在某种程度上，你运气不好。 SYSTIMESTAMP 仅返回操作系统报告的内容。时区是托管数据库的计算机系统的时区。它不是数据库时区（可能与实际数据库所在地完全无关）或会话时区或任何其他类似的时区。如果可以的话，您需要在托管数据库的操作系统中进行更改。

如果您确定托管数据库的系统位于斯里兰卡，则可以通过以下方式间接获得所需内容：

select systimestamp AT TIME ZONE 'ASIA/COLOMBO' from dual;

- user5683823

Answer 3

您可以使用以下命令在数据库级别设置时区：ALTER database。

alter database set time_zone = '+05:30';

请注意，此更改将在您重新启动数据库后生效。

shutdown immediate
startup

- Popeye

---

正如我在问题中所述，SELECT DBTIMEZONE FROM DUAL; 返回 +05:30。但我想要的是 Asia/Colombo。实际上，只要查询 SELECT SYSTIMESTAMP AS TIMEZONE FROM DUAL; 返回 04-JAN-21 02.40.50.000000000 PM ASIA/COLOMBO 而不是 04-JAN-21 02.40.50.000000000 PM +05:30，我并不关心这个查询返回什么。 - Klaus

systimestamp 报告了托管数据库的计算机操作系统的时区。数据库时区与此无关。实际上，我将我的数据库时区设置为 +00:00，并通过 select dbtimezone from dual 进行了确认；而 systimestamp 的时区为 -08:00，这是我机器上 Unix 操作系统所在的时区，即美国西海岸的时区。 - user5683823

更改 DBTIMEZONE 不会影响 SYSTIMESTAMP，请参考其他答案。 - Wernfried Domscheit

很可能你需要创建一个从偏移量到名称的转换函数才能得到想要的结果。你可以尝试从v$timezone_names中进行选择。问题是有不少于8个时区具有该偏移量。请参见此处。 - Belayer

Answer 4

Oracle的TZ_OFFSET()函数返回有效时区名称或SESSIONTIMEZONE或DBTIMEZONE函数名称相对于UTC的时区偏移量。

TZ_OFFSET(value)

TZ_OFFSET()函数接受一个参数，可以是有效的时区名称，例如“Europe/London”，SESSIONTIMEZONE或DBTIMEZONE的函数名称，或者是相对于UTC的时区偏移量（它将简单地返回自身）。

SELECT
  TZ_OFFSET( 'Europe/London' )
FROM
  DUAL;

结果将为+01:00

- Cullen

---

我想从ZoneOffset获取ZoneID。这提供了另一种方式。 - Klaus

已经有两个赞了...对于一个根本没有回答问题的答案（事实上，似乎回答者一开始就没有理解问题）。我给负一分。 - user5683823

这个回答如何解决问题？ - Wernfried Domscheit

Answer 5

这是如何获取时区的方法。

SELECT systimestamp AS DBTime,
       systimestamp at time zone 'Europe/London',
       systimestamp at time zone 'America/Sao_Paulo'from dual;

---

SELECT
  TZ_OFFSET( DBTIMEZONE )
FROM
  DUAL;

- Rk Developer

---

这个回答如何解决OP的问题？已经有两个赞了...哇！但我给了它一个负一。 - user5683823

Answer 6

SYSTIMESTAMP 返回的是数据库操作系统的时区 - 因此无法在数据库内更改。

不要将数据库操作系统的时区与 DBTIMEZONE 混淆，它们是不同的。

还要注意，时区 Asia/Colombo 与时区 +05:30 不同，尽管它们当前具有相同的 UTC 偏移量。

时区 Asia/Colombo 考虑到夏令时是否适用，它也涵盖 changes。例如，Asia/Colombo 在 2006 年从 UTC+06:00 更改为 UTC+05:30。因此，TZ_OFFSET(TIMESTAMP '2020-01-01 12:00:00 Asia/Colombo') 返回的偏移量与 TZ_OFFSET(TIMESTAMP '2000-01-01 12:00:00 Asia/Colombo') 不同。

时区 +05:30 总是比 UTC 少 5:30 小时。

你的问题不是很清楚，你想要得到什么？

- Wernfried Domscheit

---

我期望从Java查询rs.getObject("TIMEZONE", ZonedDateTime.class)时，能够获得ZoneID和ZoneOffset。假设列TIMEZONE的值为SYSTIMESTAMP，那么在Java中的ZonedDateTime对象中，ZoneID和ZoneOffset都被设置为+05:30。但是我希望ZonedDateTime对象中的ZoneID是Asia/Colombo，而ZoneOffset是+05:30。 - Klaus

如写作所述，SYSTIMESTAMP 是以数据库操作系统的时区（即 +05:30）给出的。如果您将此值插入表中，则插入和显示的就是该值 - 没有变化。如果您想要将时区 +05:30 转换为 Asia/Colombo，则选择 SYSTIMESTAMP AT TIME ZONE 'Asia/Colombo'。 - Wernfried Domscheit