将 TZ 字符串格式的时间戳转换为 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.
相关文章