Oracle解析时间戳序列自定义函数

Oracle解析时间戳序列自定义函数

解析Oracle数据库的stamp数据

将Oracle数据库的stamp数据转换为可读是时间戳

CREATE OR REPLACE FUNCTION stamp_conv_time (stamp NUMBER)
   RETURN DATE
IS
BEGIN
   RETURN TO_DATE
          (
                TO_CHAR (FLOOR (stamp / (86400 * 31 * 12)) + 1988)
             || '/'
             || TO_CHAR (FLOOR (MOD (stamp / (86400 * 31), 12)) + 1)
             || '/'
             || TO_CHAR (FLOOR (MOD (stamp / 86400, 31)) + 1)
             || ' '
             || TO_CHAR (FLOOR (MOD (stamp / 3600, 24)))
             || ':'
             || TO_CHAR (FLOOR (MOD (stamp / 60, 60)))
             || ':'
             || TO_CHAR (MOD (stamp, 60))
            ,'yyyy-mm-dd hh24:mi:ss'
          );
END;
/

应用示例

SQL> select stamp,stamp_conv_time(stamp) as conv from v$archived_log where rownum<=3;

     STAMP CONV
---------- -------------------
1034266655 2020-03-05 16:17:35
1034266659 2020-03-05 16:17:39
1034266660 2020-03-05 16:17:40

参考资料

http://blog.itpub.net/267265/viewspace-2135044/

解析Linux时间戳

在Oracle数据库中，虽然没有直接解析Linux时间戳（通常表示为自1970年1月1日以来的秒数）的内置函数，但可以通过一些简单的转换来处理这种时间戳。Linux时间戳通常是自“UNIX纪元”（1970年1月1日UTC）以来的秒数，因此你可以通过将这个时间戳转换为Oracle的日期格式来解析它。

1. 使用 TO_DATE 和 NUMTODSINTERVAL

你可以使用 TO_DATE 函数与 NUMTODSINTERVAL 函数相结合，将Linux时间戳转换为Oracle的日期格式。例如：

SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(<timestamp>, 'SECOND') AS converted_date FROM dual;

在这里，<timestamp> 是你要转换的Linux时间戳。

示例：

假设你有一个Linux时间戳 1633046400（代表2021年10月1日），可以这样转换：

SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(1633046400, 'SECOND') AS converted_date FROM dual;

2. 使用 FROM_TZ 和 CAST

如果需要将结果转换为带时区的时间戳，可以使用 FROM_TZ 和 CAST 结合实现：

SELECT FROM_TZ(CAST(TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(1633046400, 'SECOND') AS TIMESTAMP), 'UTC') AS utc_timestamp FROM dual;

3. 创建自定义函数

如果你需要频繁地解析Linux时间戳，可以考虑创建一个自定义函数。如下是一个简单的例子：

CREATE OR REPLACE FUNCTION convert_unix_timestamp(unix_ts IN NUMBER) RETURN TIMESTAMP IS
BEGIN
    RETURN TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(unix_ts, 'SECOND');
END;
/

-- 使用自定义函数
SELECT convert_unix_timestamp(1633046400) AS converted_date FROM dual;