【UTC格式】Oracle低版本实现UTC格式转换

Oracle低版本实现UTC格式转换

---

---

前言

现在Oracle版本过低是不能直接转UTC格式时间的，那么我们低版本用户可以以下这样做

---

一、创建 FUNCTION（GET_UTC_DATE）

CREATE OR REPLACE FUNCTION GET_UTC_DATE (V_DATE IN DATE)
    RETURN VARCHAR2
IS
    V_DATA   VARCHAR2 (50);
BEGIN
    SELECT CASE
               --跨年
               WHEN     TO_CHAR (V_DATE, 'MM') = 01
                    AND TO_CHAR (V_DATE, 'DD') = 01
                    AND TO_CHAR (V_DATE, 'HH24') < 08
               THEN
                      TO_NUMBER (TO_CHAR (V_DATE, 'YYYY')) - 1
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')) + 11, 2, 0)
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'DD')) + 30, 2, 0)
                   || 'T'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'HH24')) + 16, 2, 0)
                   || TO_CHAR (V_DATE, ':MI:SS')
                   || '+08:00'
               --闰年跨月
               WHEN     TO_CHAR (V_DATE, 'DD') = 01
                    AND TO_CHAR (V_DATE, 'HH24') < 08
                    AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) = 02
                    AND MOD (TO_CHAR (V_DATE, 'YYYY'), 4) = 0
                    AND MOD (TO_CHAR (V_DATE, 'YYYY'), 100) != 0
               THEN
                      TO_NUMBER (TO_CHAR (V_DATE, 'YYYY'))
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')) - 1, 2, 0)
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'DD')) + 28, 2, 0)
                   || 'T'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'HH24')) + 16, 2, 0)
                   || TO_CHAR (V_DATE, ':MI:SS')
                   || '+08:00'
               --平年跨月
               WHEN        TO_CHAR (V_DATE, 'DD') = 01
                       AND TO_CHAR (V_DATE, 'HH24') < 08
                       AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) =
                           02
                       AND MOD (EXTRACT (YEAR FROM V_DATE), 4) <> 0
                    OR (    MOD (EXTRACT (YEAR FROM V_DATE), 100) = 0
                        AND MOD (EXTRACT (YEAR FROM V_DATE), 400) <> 0)
               THEN
                      TO_NUMBER (TO_CHAR (V_DATE, 'YYYY'))
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')) - 1, 2, 0)
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'DD')) + 27, 2, 0)
                   || 'T'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'HH24')) + 16, 2, 0)
                   || TO_CHAR (V_DATE, ':MI:SS')
                   || '+08:00'
               --跨月/31天
               WHEN     TO_CHAR (V_DATE, 'DD') = 01
                    AND TO_CHAR (V_DATE, 'HH24') < 08
                    AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) IN
                            ('01',
                             '03',
                             '05',
                             '07',
                             '08',
                             '10',
                             '12')
               THEN
                      TO_NUMBER (TO_CHAR (V_DATE, 'YYYY'))
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')) - 1, 2, 0)
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'DD')) + 30, 2, 0)
                   || 'T'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'HH24')) + 16, 2, 0)
                   || TO_CHAR (V_DATE, ':MI:SS')
                   || '+08:00'
               --跨月/30天
               WHEN     TO_CHAR (V_DATE, 'DD') = 01
                    AND TO_CHAR (V_DATE, 'HH24') < 08
                    AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) IN
                            ('04',
                             '06',
                             '09',
                             '11')
               THEN
                      TO_NUMBER (TO_CHAR (V_DATE, 'YYYY'))
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')) - 1, 2, 0)
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'DD')) + 29, 2, 0)
                   || 'T'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'HH24')) + 16, 2, 0)
                   || TO_CHAR (V_DATE, ':MI:SS')
                   || '+08:00'
               --跨日
               WHEN TO_CHAR (V_DATE, 'HH24') < 08
               THEN
                      TO_CHAR (V_DATE, 'YYYY-MM')
                   || '-'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'DD')) - 1, 2, 0)
                   || 'T'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'HH24')) + 16, 2, 0)
                   || TO_CHAR (V_DATE, ':MI:SS')
                   || '+08:00'
               --当日
               ELSE
                      TO_CHAR (V_DATE, 'YYYY-MM-DD')
                   || 'T'
                   || LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'HH24')) - 8, 2, 0)
                   || TO_CHAR (V_DATE, ':MI:SS')
                   || '+08:00'
           END
               timestamp
      INTO V_DATA
      FROM DUAL;

    RETURN V_DATA;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN NULL;
END;

二、使用步骤

1.跨年日期

--代码如下（示例）
SELECT GET_UTC_DATE (
           TO_DATE ('2023-01-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))
  FROM DUAL;

--  out: 2022-12-31T23:55:55+08:00

2.跨月日期（判断 闰年 or 平年、用于计算2月天数，正常大小月）

--代码如下（示例）
--跨月日期（判断 闰年 or 平年、用于计算2月天数）
--平年
SELECT GET_UTC_DATE (
           TO_DATE ('2023-02-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))
  FROM DUAL;

--  out: 2023-01-28T23:55:55+08:00

--闰年
SELECT GET_UTC_DATE (
           TO_DATE ('2024-02-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))
  FROM DUAL;

--  out: 2024-01-29T23:55:55+08:00

--大月
SELECT GET_UTC_DATE (
           TO_DATE ('2023-03-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))
  FROM DUAL;

--  out: 2023-02-31T23:55:55+08:00

--小月
SELECT GET_UTC_DATE (
           TO_DATE ('2023-04-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))
  FROM DUAL;

--  out: 2023-03-30T23:55:55+08:00

3.跨日日期

SELECT GET_UTC_DATE (
           TO_DATE ('2023-04-02 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))
  FROM DUAL;

--  out: 2023-04-01T23:55:55+08:00

3.正常日期

SELECT GET_UTC_DATE (
           TO_DATE ('2023-04-02 09:55:55', 'YYYY-MM-DD HH24:MI:SS'))
  FROM DUAL;

--  out: 2023-04-02T01:55:55+08:00

---

总结

这篇文章到这就结束了，感受您的观看，真诚的致谢！！