我可以在Oracle中使用TO_TIMESTAMP以分钟为单位读取时区偏移量吗?

Question

好的，我正在尝试将varchar列转换为Oracle中的时间戳。

数据似乎都是'2012-02-01 11h35m34s 360'的形式，除了最后的360之外，这是很好的。绝大多数唱片都以"360度"结尾。我现在在中央时间，距离格林尼治标准时间6小时，所以我假设这是以分钟为单位的时区偏移量(出于某种原因)。

我正在寻找一种好方法来读取TO_TIMESTAMP()中的偏移量，或任何其他好方法来处理它。除了标准的偏移格式，我没有找到任何其他的信息。

Answer 1

按照这些文档中的描述，在Oracle中使用TIMESTAMP WITH TIME ZONE数据类型。

我花了一点时间才把这些放在一起，但是这里有一些函数可以用来进行转换。(免责声明:我不怎么使用Oracle，所以可能有更优的方法来做到这一点。)

CREATE OR REPLACE FUNCTION minutes_to_offset (minutes IN NUMBER)
RETURN VARCHAR
IS
BEGIN
  RETURN TO_CHAR(TRUNC(-minutes / 60)) || ':' ||
         LPAD(TO_CHAR(ABS(MOD(minutes,60))),2,'0');
END;
/
CREATE OR REPLACE FUNCTION mytimestamp (ts IN VARCHAR)
RETURN TIMESTAMP WITH TIME ZONE
IS
BEGIN
  RETURN FROM_TZ(
    TO_TIMESTAMP (SUBSTR(ts, 0, 20), 'YYYY-MM-DD HH24"h"MI"m"SS"s"')
  , minutes_to_offset(TO_NUMBER(SUBSTR(ts,22))));
END;

那么你就可以对你的字段

使用这个函数

mytimestamp('2012-02-01 11h35m34s 360')

下面是一个工作的SQL Fiddle来演示

Answer 2

本例对所有日期采用相同的格式。您可以将日期部分进一步转换为to_date()或to_timestamp()，然后concat。它与360或任何其他数字:

-- Can use To_Timestamp instead of To_Date --
 SELECT TO_CHAR(TO_DATE(dt, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')||' '||360 Final_Date
   FROM
   (
   SELECT REPLACE(REPLACE(REPLACE('2012-02-01 11h35m34s', 'h', ':'), 'm', ':'), 's', '') dt 
     FROM dual
   )
  /

输出:

2012-02-01 11:35:34 360