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

Oracle低版本实现UTC格式转换

---

---

前言

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

---

一、创建 FUNCTION（GET_UTC_DATE）

CREATE OR REPLACE FUNCTION GET_UTC_DATE (V_DATE IN DATE)RETURN VARCHAR2
ISV_DATA   VARCHAR2 (50);
BEGINSELECT CASE--跨年WHEN     TO_CHAR (V_DATE, 'MM') = 01AND TO_CHAR (V_DATE, 'DD') = 01AND TO_CHAR (V_DATE, 'HH24') < 08THENTO_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')) + 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, 'DD') = 01AND TO_CHAR (V_DATE, 'HH24') < 08AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) = 03AND MOD (TO_CHAR (V_DATE, 'YYYY'), 4) = 0AND MOD (TO_CHAR (V_DATE, 'YYYY'), 100) != 0THENTO_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') = 01AND TO_CHAR (V_DATE, 'HH24') < 08AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) =03AND MOD (EXTRACT (YEAR FROM V_DATE), 4) <> 0OR (    MOD (EXTRACT (YEAR FROM V_DATE), 100) = 0AND MOD (EXTRACT (YEAR FROM V_DATE), 400) <> 0)THENTO_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') = 01AND TO_CHAR (V_DATE, 'HH24') < 08AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) IN('02','04','06','09','11')THENTO_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') = 01AND TO_CHAR (V_DATE, 'HH24') < 08AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) IN('01','05','07','08','10','12')THENTO_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') < 08THENTO_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'--当日ELSETO_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'ENDtimestampINTO V_DATAFROM DUAL;RETURN V_DATA;
EXCEPTIONWHEN OTHERSTHENRETURN 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-03-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))FROM DUAL;--  out: 2023-02-28T23:55:55+08:00--闰年
SELECT GET_UTC_DATE (TO_DATE ('2024-03-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))FROM DUAL;--  out: 2024-02-29T23: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-31T23:55:55+08:00--小月
SELECT GET_UTC_DATE (TO_DATE ('2023-05-01 07:55:55', 'YYYY-MM-DD HH24:MI:SS'))FROM DUAL;--  out: 2023-04-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

---

总结

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