問題描述
如何從包含 Oracle 中時區偏移的日期/時間字符串中獲取 UTC 日期/時間 (How to get UTC date/time from a date/time string that contains timezone offset in Oracle)
給定這樣的字符串：'2015‑11‑23T07:00:10.563‑04:00' 我正在嘗試確定我期望的 UTC 日期/時間：'2015‑11‑23 11:00 :10.5630000'
我嘗試了以下方法：
SELECT sys_extract_utc(
to_timestamp_tz('2015‑11‑23T07:00:10.563‑04:00',
'yyyy‑mm‑dd"T"hh24:mi:ss.FF TZH:TZM')
)
FROM DUAL;
但這會產生結果：
2015/11/23 03:00:10.563000000
即使我將時區偏移量更改為正值，我也會得到同樣的結果。我一定是誤會了，所以非常感謝您的幫助。
參考解法
方法 1:
There is no space between fractional seconds and timezone hour. So remove space from you pattern, and it'll give desired result.
SQL> SELECT sys_extract_utc(to_timestamp_tz('2015‑11‑23T07:00:10.563‑04:00', 'yyyy‑mm‑dd"T"hh24:mi:ss.FFTZH:TZM'))
FROM DUAL;
SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ('2015‑11‑23T07:00:10.563‑04:00','YYYY‑MM‑DD
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
23‑NOV‑15 11.00.10.563000000 AM
方法 2:
Fixed it ‑ the space character between ".....hh24:mi:ss.FF TZH:TZM" was causing the problem.
This now works perfectly:
SELECT sys_extract_utc(to_timestamp_tz('2015‑11‑23T07:00:10.563+04:00', 'yyyy‑mm‑dd"T"hh24:mi:ss.FFTZH:TZM')) FROM DUAL;