VARCHAR2 ORACLE 12c 中的时间戳转换

Question

>我在表 A 中有一列作为

select create_time from table_a;

值为

08-MAR-19 08.23.47.897000000 PM GMT.

出于某些业务目的，此列已标记为VARCHAR2。现在我正在尝试获取此列并将值转换为 TIMESTAMP 出于某种目的，如下所示：

SELECT TO_TIMESTAMP(create_time, 'DD-MON-YYYY HH.MI.SS.FF AM') from table_a;

但是我收到错误：

ORA-01830: date format picture ends before converting entire input string

有人可以帮助我将此 varchar 数据转换为时间戳。我尝试这样做的原因是，我需要将这个时间从一个时区转换为另一个时区： 例如：

SELECT FROM_TZ(TO_TIMESTAMP(create_time, 'DD-MON-YYYY HH.MI.SS.FF AM'), 'UTC') AT TIME ZONE 'CET' from table_a;

Answer 1

SQL> select replace('08-MAR-19 08.23.47.897000000 PM GMT','GMT','') AS RESULT from dual 
;
RESULT
--------------------------------
08-MAR-19 08.23.47.897000000 PM
SQL> select to_timestamp(replace('08-MAR-19 08.23.47.897000000 PM GMT','GMT','')) as RESULT from dual ;
RESULT
---------------------------------------------------------------------------
08-MAR-19 08.23.47.897000000 PM
SQL> select from_tz(to_timestamp(replace('08-MAR-19 08.23.47.897000000 PM GMT','GMT','')),'UTC') AT TIME ZONE 'CET' AS RESULT from dual ;
RESULT
---------------------------------------------------------------------------
08-MAR-19 09.23.47.897000000 PM CET
SQL>

Answer 2

格式字符串中不考虑末尾GMT的四个字符

SELECT TO_TIMESTAMP('08-MAR-19 08.23.47.897000000 PM GMT', 'DD-MON-YYYY HH.MI.SS.FF AM') from dual;

以下处理：

SELECT TO_TIMESTAMP(substr(create_time, 1, LENGTH(create_time) -4), 'DD-MON-YYYY HH.MI.SS.FF AM') t from dual;

Answer 3

试试这个。

select  TO_TIMESTAMP( REPLACE(ts, 'GMT', '')) from test_timestamp;

Answer 4

使用REPLACE删除GMT

WITH A AS (SELECT REPLACE('08-MAR-19 08.23.47.897000000 PM GMT','GMT','') AS D FROM DUAL)
SELECT TO_TIMESTAMP (D, 'DD-MON-YYYY HH.MI.SS.FF AM')
FROM A

Answer 5

执行以下操作：

to_timestamp(replace(create_time ,'GMT', null))

依赖于时间戳格式(尤其是具有 RR 年份掩码)和月份缩写语言的 NLS 设置。这样做会更安全：

to_timestamp(replace(create_time, ' GMT', null),
'DD-MON-RR HH.MI.SS.FF AM', 'NLS_DATE_LANGUAGE=ENGLISH')

如果时区并不总是 GMT，但始终是有效且可识别的区域(例如，不是 BST)，那么您可能需要保留包括该区域在内的完整日期/时间：

to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
'NLS_DATE_LANGUAGE=ENGLISH')

如果您希望将其作为纯时间戳，您可以强制转换它，可能先更改为特定区域：

cast(to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
'NLS_DATE_LANGUAGE=ENGLISH') as timestamp)

或

cast(to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
'NLS_DATE_LANGUAGE=ENGLISH') at time zone 'Asia/Tokyo' as timestamp)

或规范化为 UTC(当然，这不会影响 GMT 值，因为它们本质上是相同的)：

sys_extract_utc(to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
'NLS_DATE_LANGUAGE=ENGLISH'))

数据库<>小提琴