将 TZ 字符串格式的时间戳转换为 Oracle 中的时间戳 [英] Convert TimeStamp in TZ string format to TimeStamp in 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.

这篇关于将 TZ 字符串格式的时间戳转换为 Oracle 中的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！