oracle - 如何将带时区的字符串转换为日期

Question

如何在Oracle中将下面的字符串转换为日期

Wed Jan 13 23:01:24 GMT 2016

在下面尝试过，这个获取的日期为

SELECT TO_CHAR(SYSDATE, 'DY MON DD HH24:MM:SS YYYY') FROM dual;

FRI AUG 26 14:08:04 2016

最终，想要这样的东西

SELECT TO_CHAR('Wed Jan 13 23:01:24 GMT 2016', 'DY MON DD HH24:MM:SS ??? YYYY') FROM dual;

Answer 1

如果时区始终是公认的缩写，您可以:

select to_timestamp_tz('Wed Jan 13 23:01:24 GMT 2016', 'Dy Mon DD HH24:MI:SS TZD YYYY')
from dual;

TO_TIMESTAMP_TZ('WEDJAN1323:01:24GMT2016','DYMONDDHH24:MI:SSTZDYYYY')
---------------------------------------------------------------------
2016-01-13 23:01:24 EUROPE/LONDON                                    

您不能直接转换为日期，因为 to_date() 中不允许使用时区格式元素。如果您有一个固定值 - 始终是格林威治标准时间 - 您可以通过将其视为文字来忽略它，但您没有这样做。

如果您希望它作为时间戳或日期，而不是带有时区的时间戳，您需要决定如何转换它。您可以假定它是本地时间，并通过强制转换来忽略时区，或者您可以将其调整为特定时区，例如世界标准时间。有多种方法，这里有一些带有 CTE 的方法来提供您的样本，还有一个在另一个区域(好吧，无论如何在夏季，所以您会得到不同的字符串):

with t (str) as (
  select 'Wed Jan 13 23:01:24 GMT 2016' from dual
  union all select 'Fri Aug 26 19:53:27 BST 2016' from dual
)
select to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as tstz,
  cast(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as timestamp) as ts,
  cast(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as date) as dt,
  sys_extract_utc(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY')) as tsutc
from t;

TSTZ                              TS                  DT                  TSUTC             
--------------------------------- ------------------- ------------------- -------------------
2016-01-13 23:01:24 EUROPE/LONDON 2016-01-13 23:01:24 2016-01-13 23:01:24 2016-01-13 23:01:24
2016-08-26 19:53:27 EUROPE/LONDON 2016-08-26 19:53:27 2016-08-26 19:53:27 2016-08-26 18:53:27

当然，具体如何处理取决于您真正需要什么。

---

不幸的是，这并不总是适用于缩写； Oracle 不一定能识别您在 Unix date 命令输出中看到的值，而且它能识别的值并不总是可用。更改 session 时区可能会破坏它:

alter session set time_zone = 'America/Los_Angeles';
select to_timestamp_tz('Wed Jan 13 23:01:24 GMT 2016', 'Dy Mon DD HH24:MI:SS TZD YYYY')
from dual;

ORA-01857: not a valid time zone

您可以将 session 时区更改为能够识别它的时区(欧洲/伦敦)，但这是一种 hack，无论如何都不适用于所有值。缩写可能意味着不止一件事，这无济于事。

如果您有一个已知预期值的列表并且知道它们对您真正代表什么，您可以将缩写换成一个区域，但它并没有真正扩展:

select to_timestamp_tz(
    replace(replace('Wed Jan 13 23:01:24 GMT 2016', 'GMT', 'Europe/London'),
      'BST', 'Europe/London'),
    'Dy Mon DD HH24:MI:SS TZR YYYY') from dual;

或具有多种输出格式:

with t1 (str) as (
  select 'Wed Jan 13 23:01:24 GMT 2016' from dual
  union all select 'Fri Aug 26 19:53:27 BST 2016' from dual
),
t2 (adj_str) as (
  select replace(replace(str, 'GMT', 'Europe/London'), 'BST', 'Europe/London')
  from t1
)
select to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as tstz,
  cast(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as timestamp) as ts,
  cast(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as date) as dt,
  sys_extract_utc(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY')) as tsutc
from t2;

您需要为您期望的每个缩写调用嵌套的 replace 调用(或 regexp_replace 以稍微减少重复)；或者可以有一个函数来隐藏你的主要查询中的困惑。