将ORACLE中的DATE转换为带有偏移量的TIMESTAMP WITH TIME ZONE

Question

我需要在查询中将日期值转换为带时区的时间戳(TIMESTAMP WITH TIME ZONE)，但目前我得到的是时区地区('Europe/Paris')，这不能被EF使用。

例如，在执行以下操作时：

select CAST(FECHA AS TIMESTAMP WITH TIME ZONE) from test;

我目前得到的输出是：

07/03/14 09:22:00,000000000 EUROPE/PARIS

但我希望它像这样：

07/03/14 09:22:00,000000000 +01:00

有什么想法可以完成这个任务吗？

- Farlop

---

当您使用CAST({DATE} AS TIMESTAMP WITH TIME ZONE)时，Oracle会采用您当前的SESSIONTIMEZONE - 显然设置为Europe/Paris。 - Wernfried Domscheit

一个重要的问题是，您是否希望结果为文本还是实际的TIMESTAMP WITH TIME ZONE，并且使用结果的客户端将理解该类型（因此应该能够理解EUROPE/PARIS对于这个特定时间有+01:00偏移量？ - Hilarion

Answer 1

您可以将DATE转换为TIMESTAMP，然后使用FROM_TZ将此时间戳转换为带有时区的时间戳：

SQL> SELECT from_tz(CAST (SYSDATE AS TIMESTAMP), '+01:00') tz FROM dual;
TZ
-------------------------------------------------
07/03/14 09:47:06,000000 +01:00

- Vincent Malgrat

---

太棒了！这使我们能够根据数据来源设置所需的时区。非常感谢！ - Farlop

Answer 2

使用 @Vincent Malgrat 的解决方案，您需要获取 TIMEZONE_HOUR 并将其格式化以在查询中使用。我不知道是否有任何机会自动化这个过程。

我可以建议您嵌套一些函数。这不是最清晰的解决方案，但对我很有效。

SELECT TO_TIMESTAMP_TZ(TO_CHAR(CAST(FECHAHORA AS TIMESTAMP WITH TIME ZONE), 'DD-MM-YY HH24:MI:SS TZH:TZM'), 'DD-MM-YY HH24:MI:SS TZH:TZM' )FROM TEST;

结果会类似于以下内容：

03/03/14 09:58:02,000000000 +01:00

Regards!

- RandomUser

---

实际上，这是一个更好的解决方案！使用这个解决方案，就不需要在SQL脚本中硬编码时区。 - Rick

将 DATE 转换为字符串再转换回时间戳值是毫无意义的。而且结果取决于当前用户会话 SESSIONTIMEZONE。尝试执行 ALTER SESSION SET TIME_ZONE = '+12:00';，然后结果将是错误的。 - Wernfried Domscheit

@WernfriedDomscheit，双重转换是将一个时区地区转换为一个时区偏移量所必需的。有关差异的更多信息，请参见此处https://stackoverflow.com/tags/timezone/info。 对于您的示例，输出与输入相同，因为输入和输出都是偏移量。这个答案隐含地使用了会话的时区。 - Hamish Carpenter

是的，时区和时区偏移之间的差异很重要。这并不意味着您需要进行两种类型的转换才能实现结果。重要的是，使用实际时区（而不仅仅是偏移量）的带有时区时间戳实际上比只有偏移量的带有时区时间戳更精确。您可以从两者中获取偏移量，但是当仅具有偏移量时，无法获取完整的时区（区域）。 - Hilarion

Answer 3

在你的SELECT语句之前使用ALTER SESSION SET TIME_ZONE = '+01:00';

- Dan

---

合法的答案，但一些解释会很有用。 - Wernfried Domscheit