将 TZ 字符串格式的时间戳转换为 Oracle 中的时间戳

2022-01-13 00:00:00 timestamp type-conversion oracle

我有字符串格式的时间戳,即2015-03-24T07:08:24.000Z",如何从 ORACLE 中的给定字符串将其转换回时间戳,即 2015-03-24T07:08:24.000Z?

I have timestamp in string format i.e. "2015-03-24T07:08:24.000Z", how can I convert it back to the timestamp i.e. 2015-03-24T07:08:24.000Z from the given string in ORACLE?

推荐答案

可能固定的 T 和 Z 有点让你困惑,因为它们不正常 日期时间格式模型元素.但是文档说:

Presumably the fixed T and Z are confusing you a bit, as they aren't normal datetime format model elements. But the documentation says:

您可以在日期格式模型中包含这些字符:

You can include these characters in a date format model:

  • 连字符、斜杠、逗号、句号和冒号等标点符号
  • 字符字面量,用双引号括起来

因此,您在格式模型中将 T 和 Z 括在双引号中,分别为 "T""Z".

So you enclose the T and Z in double-quotes, as "T" and "Z", in your format model.

如果您对时区不感兴趣,可以使用 to_timestamp() 函数:

If you aren't interersted in the timezone you can use the to_timestamp() function:

to_timestamp('2015-03-24T07:08:24.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')

或者,如果您想使用时区,您可以使用 to_timestamp_tz() 函数,它将默认为您当前的会话时区(因为您实际上并未在此处指定一个,Z 不会被解释为 Zulu/UTC):

Or if you want to have with the timezone you can use the to_timestamp_tz() function, which will default to your current session timezone (as you aren't actually specifying one here, the Z isn't interpreted as Zulu/UTC):

to_timestamp_tz('2015-03-24T07:08:24.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')

如果您希望它带有时区并希望指定它是 UTC,您可以使用 from_tz() 函数:

If you want it with a timezone and want to specify that it is UTC you can force that with the from_tz() function:

from_tz(to_timestamp('2015-03-24T07:08:24.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')), 'UTC')

要查看它们产生的差异,请指定会话的时区作为演示:

To see the difference those produce, specifying a timezone for the session as a demo:

alter session set time_zone = 'America/New_York';

select to_timestamp('2015-03-24T07:08:24.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') as no_tz,
  to_timestamp_tz('2015-03-24T07:08:24.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') as default_tz,
  from_tz(to_timestamp('2015-03-24T07:08:24.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'), 'UTC') as utc_tz
from dual;

NO_TZ
--------------------------------------------------
DEFAULT_TZ
--------------------------------------------------
UTC_TZ
--------------------------------------------------
24-MAR-15 07.08.24.000000000                       
24-MAR-15 07.08.24.000000000 AMERICA/NEW_YORK      
24-MAR-15 07.08.24.000000000 UTC                  

我假设 Z 是固定的,因此您得到的值始终代表 UTC;如果您实际上在需要转换的值中获得了不同的时区,那么您需要提取并应用这些值 - 这是可行的,但很困难,并且不值得进入,除非您确实遇到这种情况.

I'm assuming the Z is fixed and the values you get are therefore always represent UTC; if you actually get different timezones in the values you need to convert then you'll need to extract and apply those - which is doable, but difficult, and not worth going into unless you actually have that situation.

相关文章