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')) + 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') = 01
AND TO_CHAR (V_DATE, 'HH24') < 08
AND LPAD (TO_NUMBER (TO_CHAR (V_DATE, 'MM')), 2, 0) = 03
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) =
03
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
('02',
'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')) + 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
('01',
'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')) + 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-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
总结
这篇文章到这就结束了，感受您的观看，真诚的致谢！！