“日期&时间戳”基本处理：
--例一：
SELECT SYSTIMESTAMP AS 当前时间,
-- 可外套一层TO_NUMBER转数字类型
-- 如：TO_NUMBER(TO_CHAR(SYSTIMESTAMP,'HH24'))
TO_CHAR(SYSTIMESTAMP,'HH24') AS 时,
TO_CHAR(SYSTIMESTAMP,'MI') AS 分,
TO_CHAR(SYSTIMESTAMP,'SS') AS 秒,
TO_CHAR(SYSTIMESTAMP,'DD') AS 日,
TO_CHAR(SYSTIMESTAMP,'MM') AS 月,
TO_CHAR(SYSTIMESTAMP,'YYYY') AS 年,
-- 此处若2023-02-18 则年内第几天是"049"
TO_CHAR(SYSTIMESTAMP,'DDD') AS 年内第几天,
TRUNC(SYSTIMESTAMP,'DD') AS 日初,
-- 周日为一天的开始
TRUNC(SYSTIMESTAMP,'DAY') AS 周初,
TRUNC(SYSTIMESTAMP,'D') AS 周初,
TRUNC(SYSTIMESTAMP,'MM') AS 月初,
-- 月末的当前时间 如 2023-02-28 21:43:23
-- 如不需要时间戳外套一层TRUNC(LAST_DAY(SYSDATE),'DD')
LAST_DAY(SYSDATE) AS 月末的当前时间,
ADD_MONTHS(TRUNC(SYSTIMESTAMP,'MM'),1) AS 下月初,
TRUNC(SYSTIMESTAMP,'YYYY') AS 年初,
TO_CHAR(SYSTIMESTAMP,'DAY') AS 周几
FROM DUAL
--例二：
SELECT HIREDATE AS 当前时间,
-- 可外套一层TO_NUMBER转数字类型
-- 如：TO_NUMBER(TO_CHAR(HIREDATE,'HH24'))
TO_CHAR(HIREDATE,'HH24') AS 时,
TO_CHAR(HIREDATE,'MI') AS 分,
TO_CHAR(HIREDATE,'SS') AS 秒,
TO_CHAR(HIREDATE,'DD') AS 日,
TO_CHAR(HIREDATE,'MM') AS 月,
TO_CHAR(HIREDATE,'YYYY') AS 年,
-- 此处若2023-02-18 则年内第几天是"049"
TO_CHAR(HIREDATE,'DDD') AS 年内第几天,
TRUNC(HIREDATE,'DD') AS 日初,
-- 周日为一天的开始
TRUNC(HIREDATE,'DAY') AS 周初,
TRUNC(HIREDATE,'MM') AS 月初,
-- 月末的当前时间 如 2023-02-28 21:43:23
-- 如不需要时间戳外套一层TRUNC(LAST_DAY(SYSDATE),'DD')
LAST_DAY(SYSDATE) AS 月末的当前时间,
ADD_MONTHS(TRUNC(HIREDATE,'MM'),1) AS 下月初,
TRUNC(HIREDATE,'YYYY') AS 年初,
TO_CHAR(HIREDATE,'DAY') AS 周几
FROM (SELECT DATE'2023-02-18'+ 30/24/60/60 + 20/24/60 +5/24 AS HIREDATE FROM DUAL)A
日期/时间戳 相减 获取天数
* 日期与日期 相减 返回天数
时间戳与日期， 或 时间戳与时间戳 相减 返回INTERVAL
表TT字段：
| 相距天数 | 0.0112268518518518518518518518518518518519 |
| 相距小时数 | 0.2694444444444444444444444444444444444456 |
| 相距分钟数 | 16.16666666666666666666666666666666666674 |
| 相距秒数 | 970.000000000000000000000000000000000004 |
| INTERVAL1 | +000000000 00:16:09.536000 |
| INTERVAL2 | +000000000 23:43:50.464000 |
| INTERVAL3 | +000000000 23:43:50.464000 |
| 日期 | 2023-02-20 00:00:00 |
| 时间戳 | 2023-02-18 23:43:50.464000 +08:00 |
WITH TT AS ( SELECT
-- 日期 减 日期 = 返回天
DATE'2023-02-19' - SYSDATE AS 相距天数,
(DATE'2023-02-19' - SYSDATE) *24 AS 相距小时数,
(DATE'2023-02-19' - SYSDATE) *24*60 AS 相距分钟数,
(DATE'2023-02-19' - SYSDATE) *24*60*60 AS 相距秒数,
-- 与时间戳计算 返回INTERVAL （法一示例）
DATE'2023-02-19' - SYSTIMESTAMP AS INTERVAL1,
SYSTIMESTAMP - DATE'2023-02-18' AS INTERVAL2,
SYSTIMESTAMP - TO_TIMESTAMP(DATE'2023-02-18' ) AS INTERVAL3,
-- 两个时间戳加减 提取天数（法二示例）
DATE'2023-02-20' AS 日期,
SYSTIMESTAMP AS 时间戳
FROM DUAL )| 法一 | |||||||
| 提取天数 | 提取小时数 | 提取分钟数 | 提取秒数 | 相距毫秒数 | 相距秒数 | 相距分钟数 | 相距小时数 |
| 0 | 0 | 9 | 33.784 | 573784 | 573.784 | 9 | 0 |
| 法二 | |||||||
| 相距毫秒数_法2 | 相距秒数_法2 | 相距分钟数_法2 | 相距小时数_法2 | ||||
| 86885259 | 86885.259 | 1448 | 24 | ||||
WITH TT AS ( SELECT
-- 日期 减 日期 = 返回天
DATE'2023-02-19' - SYSDATE AS 相距天数,
(DATE'2023-02-19' - SYSDATE) *24 AS 相距小时数,
(DATE'2023-02-19' - SYSDATE) *24*60 AS 相距分钟数,
(DATE'2023-02-19' - SYSDATE) *24*60*60 AS 相距秒数,
-- 与时间戳计算 返回INTERVAL （法一示例）
DATE'2023-02-19' - SYSTIMESTAMP AS INTERVAL1,
SYSTIMESTAMP - DATE'2023-02-18' AS INTERVAL2,
SYSTIMESTAMP - TO_TIMESTAMP(DATE'2023-02-18' ) AS INTERVAL3,
-- 两个时间戳加减 提取天数（法二示例）
DATE'2023-02-20' AS 日期,
SYSTIMESTAMP AS 时间戳
FROM DUAL )
-- 对INTERVAL格式提取
-- 法一 相减后计算间隔
SELECT EXTRACT(DAY FROM INTERVAL1) AS 提取天数,
EXTRACT(HOUR FROM INTERVAL1) AS 提取小时数,
EXTRACT(MINUTE FROM INTERVAL1) AS 提取分钟数,
EXTRACT(SECOND FROM INTERVAL1) AS 提取秒数,
--计算相距毫秒数
EXTRACT(DAY FROM INTERVAL1) *24*60*60*1000 +
EXTRACT(HOUR FROM INTERVAL1) *60*60*1000 +
EXTRACT(MINUTE FROM INTERVAL1) *60*1000 +
EXTRACT(SECOND FROM INTERVAL1) *1000 AS 相距毫秒数,
--计算相距秒数
EXTRACT(DAY FROM INTERVAL1) *24*60*60 +
EXTRACT(HOUR FROM INTERVAL1) *60*60 +
EXTRACT(MINUTE FROM INTERVAL1) *60 +
EXTRACT(SECOND FROM INTERVAL1) AS 相距秒数,
--计算相距分钟数
EXTRACT(DAY FROM INTERVAL1) *24*60 +
EXTRACT(HOUR FROM INTERVAL1) *60 +
EXTRACT(MINUTE FROM INTERVAL1) AS 相距分钟数,
--计算相距小时数
EXTRACT(DAY FROM INTERVAL1) *24 +
EXTRACT(HOUR FROM INTERVAL1) 相距小时数,
-- 法二 边减边算间隔
--计算相距毫秒数
EXTRACT(DAY FROM 日期-时间戳) *24*60*60*1000 +
EXTRACT(HOUR FROM 日期-时间戳) *60*60*1000 +
EXTRACT(MINUTE FROM 日期-时间戳) *60*1000 +
EXTRACT(SECOND FROM 日期-时间戳) *1000 AS 相距毫秒数_法2,
--计算相距秒数
EXTRACT(DAY FROM 日期-时间戳) *24*60*60 +
EXTRACT(HOUR FROM 日期-时间戳) *60*60 +
EXTRACT(MINUTE FROM 日期-时间戳) *60 +
EXTRACT(SECOND FROM 日期-时间戳) AS 相距秒数_法2,
--计算相距分钟数
EXTRACT(DAY FROM 日期-时间戳) *24*60 +
EXTRACT(HOUR FROM 日期-时间戳) *60 +
EXTRACT(MINUTE FROM 日期-时间戳) AS 相距分钟数_法2,
--计算相距小时数
EXTRACT(DAY FROM 日期-时间戳) *24 +
EXTRACT(HOUR FROM 日期-时间戳) 相距小时数_法2
FROM TT周的运算
* TO_CHAR(SYSDATE,'DY')， TO_CHAR(SYSDATE,'DAY') 两个参数受环境变量影响
日常建议使用 TO_CHAR(SYSDATE,'D')
ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
SELECT SYSDATE AS 当前日期,
TO_CHAR(SYSDATE,'DY') AS 周几1,
TO_CHAR(SYSDATE,'DAY') AS 周几2,
TO_CHAR(SYSDATE,'D') AS 周几3
FROM DUAL;
ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';
SELECT SYSDATE AS 当前日期,
TO_CHAR(SYSDATE,'DY') AS 周几1,
TO_CHAR(SYSDATE,'DAY') AS 周几2,
TO_CHAR(SYSDATE,'D') AS 周几3
FROM DUAL;需求: 本月第一个周二
法一： 枚举月初前七天，取TO_CHAR(COLUMN,'D') = 3
SELECT * FROM ( SELECT TRUNC(SYSDATE,'MM') AS 月初,
(TRUNC(SYSDATE,'MM') + (LEVEL -1)) AS 月初后第一个星期,
TO_CHAR((TRUNC(SYSDATE,'MM') + LEVEL),'D') AS 周几标识
FROM DUAL
CONNECT BY LEVEL <=7)TT
WHERE 周几标识=3
法二：运用NEXT_DAY()函数 NEXT_DAY(COLUMN, 3)
SELECT TT. *, NEXT_DAY(月初后第一个星期,3) FROM (
( SELECT TRUNC(SYSDATE,'MM') AS 月初,
(TRUNC(SYSDATE,'MM') + (LEVEL - 2)) AS 月初后第一个星期,
TO_CHAR((TRUNC(SYSDATE,'MM') + LEVEL),'D') AS 周几标识
FROM DUAL
CONNECT BY LEVEL <=8))TT需求：计算今年周二的天数
SELECT 周几,COUNT(1) AS CNT
FROM ( SELECT TRUNC(SYSDATE, 'YYYY') AS 年初,
TRUNC(SYSDATE, 'YYYY') + (LEVEL -1 ) AS 枚举日期列表,
TO_CHAR(TRUNC(SYSDATE, 'YYYY') + (LEVEL -1 ),'DAY') AS 周几
FROM( SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),12) -
TRUNC(SYSDATE, 'YYYY') AS CNT
FROM DUAL) TT
CONNECT BY LEVEL <= CNT )
GROUP BY 周几
ORDER BY 周几
需求：是否为闰年
法一：二月最后一天是否为29号
法二：计算全年是否为366天
SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),1)) AS 二月末 FROM DUAL;
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),12) - TRUNC(SYSDATE, 'YYYY') AS CNT FROM DUAL;创建当月月历
WITH TMP1 AS
-- 选定一个日期
(SELECT TRUNC(SYSDATE,'DD') AS CUR_DATE FROM DUAL),
-- 日期的月初及月末时间
TMP2 AS (SELECT TRUNC(CUR_DATE,'MM') AS MON_BEGIN, ADD_MONTHS(TRUNC(CUR_DATE,'MM'), 1) -1 AS MON_END FROM TMP1),
-- 枚举月份日期列表
TMP3 AS ( SELECT MON_BEGIN + (LEVEL -1) AS DATE_LIST FROM TMP2 CONNECT BY LEVEL<= (MON_END-MON_BEGIN+1)),
TMP4 AS (SELECT TRUNC(DATE_LIST, 'DAY') WEK_BEGIN, TO_CHAR(DATE_LIST,'DD') DAY,TO_CHAR(DATE_LIST,'D') WEKDAY, DATE_LIST FROM TMP3)
SELECT MAX(DECODE(WEKDAY,1,DAY)) AS SUN,
MAX(DECODE(WEKDAY,2,DAY)) AS MON,
MAX(DECODE(WEKDAY,3,DAY)) AS TUE,
MAX(DECODE(WEKDAY,4,DAY)) AS WED,
MAX(DECODE(WEKDAY,5,DAY)) AS THU,
MAX(DECODE(WEKDAY,6,DAY)) AS FRI,
MAX(DECODE(WEKDAY,7,DAY)) AS SAT
FROM TMP4
GROUP BY WEK_BEGIN
ORDER BY WEK_BEGIN创建当年月历
WITH TMP1 AS
-- 选定一个日期
(SELECT TRUNC(SYSDATE,'YYYY') AS YEAR_BEGIN FROM DUAL),
-- 日期的年初及年末时间
TMP2 AS (SELECT YEAR_BEGIN,ADD_MONTHS(YEAR_BEGIN, 12) -1 AS YEAR_END FROM TMP1),
-- 枚举当年日期列表
TMP3 AS (SELECT YEAR_BEGIN + (LEVEL -1) AS DATE_LIST FROM TMP2 CONNECT BY LEVEL<= (YEAR_END-YEAR_BEGIN+1)),
TMP4 AS (SELECT TO_CHAR(DATE_LIST,'MM') MONTHS,TRUNC(DATE_LIST, 'DAY') WEK_BEGIN, TO_CHAR(DATE_LIST,'DD') DAY,TO_CHAR(DATE_LIST,'D') WEKDAY, DATE_LIST FROM TMP3)
--SELECT * FROM TMP4
SELECT MONTHS,
MAX(DECODE(WEKDAY,1,DAY)) AS SUN,
MAX(DECODE(WEKDAY,2,DAY)) AS MON,
MAX(DECODE(WEKDAY,3,DAY)) AS TUE,
MAX(DECODE(WEKDAY,4,DAY)) AS WED,
MAX(DECODE(WEKDAY,5,DAY)) AS THU,
MAX(DECODE(WEKDAY,6,DAY)) AS FRI,
MAX(DECODE(WEKDAY,7,DAY)) AS SAT
FROM TMP4
GROUP BY MONTHS,WEK_BEGIN
ORDER BY MONTHS,WEK_BEGIN
生成日期序列补空
WITH T1 AS ( SELECT 2023 - LEVEL AS YEAR_BEFORE FROM DUAL CONNECT BY LEVEL <=10 ),
T2 AS ( SELECT YEAR_BEFORE,trunc(dbms_random.value(0,100)) as CNT FROM T1 WHERE MOD(YEAR_BEFORE,2)=0 )
SELECT T1.YEAR_BEFORE,COALESCE(T2.CNT,0) CNT FROM T1 LEFT JOIN T2 ON T1.YEAR_BEFORE=T2.YEAR_BEFORE
ORDER BY YEAR_BEFORE
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
