本文主要是介绍Oracle【低版本实现UTC格式转换】，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Oracle低版本实现UTC格式转换
文章目录
- Oracle低版本实现UTC格式转换
- 前言
- 一、创建 FUNCTION（GET_UTC_DATE）
- 二、使用步骤
- 1.跨年日期
- 2.跨月日期（判断 闰年 or 平年、用于计算2月天数，正常大小月）
- 3.跨日日期
- 3.正常日期
- 总结
前言
现在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
总结
这篇文章到这就结束了，感受您的观看，真诚的致谢！！
这篇关于Oracle【低版本实现UTC格式转换】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
