将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，则可以使用

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屋！