现在Oracle版本过低是不能直接转UTC格式时间的,那么我们低版本用户可以以下这样做
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;
--代码如下(示例)
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
--代码如下(示例)
--跨月日期(判断 闰年 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
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
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
这篇文章到这就结束了,感受您的观看,真诚的致谢!!