数据库|时间处理 DB2时间函数大全

📅 时间都去哪儿了？DB2时间函数大全帮你轻松拿捏数据时刻！

刚入职的小王盯着屏幕上的报表发愁："领导要上周三到本周二的销售数据，可数据库里存的是时间戳啊！" 别急，今天我们就来解锁DB2时间处理的魔法技能包！✨

🕰️ 基础时间函数三件套

CURRENT DATE/TIME/TIMESTAMP

这三个好兄弟是你的入门必备：

SELECT 
    CURRENT DATE AS "今天日期",  -- 2025-08-15
    CURRENT TIME AS "现在时间",  -- 14:30:45
    CURRENT TIMESTAMP AS "此刻完整时间"  -- 2025-08-15 14:30:45.123456
FROM SYSIBM.SYSDUMMY1;

👉 小技巧：SYSIBM.SYSDUMMY1是DB2的虚拟表，专门用来测试函数

DATE/TIME/TIMESTAMP构造函数

把散装时间组装起来：

SELECT 
    DATE('2025-08-20') AS "指定日期",
    TIME('23:59:59') AS "午夜前一秒",
    TIMESTAMP('2025-12-31 23:59:59') AS "跨年时刻"
FROM SYSIBM.SYSDUMMY1;

🔍 时间提取大师

EXTRACT函数

像拆快递一样拆解时间：

SELECT 
    EXTRACT(YEAR FROM CURRENT TIMESTAMP) AS "今年",
    EXTRACT(MONTH FROM TIMESTAMP('2025-08-15 09:30:00')) AS "八月",
    EXTRACT(DAY FROM DATE('2025-08-20')) AS "20号"
FROM SYSIBM.SYSDUMMY1;

快捷提取方式

DB2还贴心地准备了快捷键：

SELECT 
    YEAR(CURRENT DATE) AS "年",
    MONTH(CURRENT DATE) AS "月",
    DAY(CURRENT DATE) AS "日",
    HOUR(CURRENT TIME) AS "时",
    MINUTE(CURRENT TIME) AS "分",
    SECOND(CURRENT TIME) AS "秒"
FROM SYSIBM.SYSDUMMY1;

⏳ 时间计算神器

日期加减法

-- 计算三天后的日期
SELECT CURRENT DATE + 3 DAYS AS "三天后" FROM SYSIBM.SYSDUMMY1;
-- 计算两个月前的今天
SELECT CURRENT DATE - 2 MONTHS AS "两月前" FROM SYSIBM.SYSDUMMY1;
-- 精确到分钟的计算
SELECT CURRENT TIMESTAMP + 15 MINUTES AS "15分钟后" FROM SYSIBM.SYSDUMMY1;

日期差值计算

-- 计算两个日期相差的天数
SELECT DAYS(DATE('2025-12-31')) - DAYS(CURRENT DATE) AS "距离元旦还有几天"
FROM SYSIBM.SYSDUMMY1;
-- 精确到秒的差值
SELECT TIMESTAMPDIFF(2, CHAR(TIMESTAMP('2025-08-20 18:00:00') - CURRENT TIMESTAMP)) 
AS "距离8.20还有多少秒"
FROM SYSIBM.SYSDUMMY1;

👉 注意：TIMESTAMPDIFF第一个参数2表示秒，1=微秒，4=分钟，8=小时，16=天...

📆 日期格式化秀

VARCHAR_FORMAT函数

让时间穿上漂亮外衣：

SELECT 
    VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYY年MM月DD日 HH24:MI:SS') AS "中文格式",
    VARCHAR_FORMAT(CURRENT DATE, 'MM/DD/YY') AS "美式日期",
    VARCHAR_FORMAT(CURRENT TIME, 'HH.MI.SS') AS "点分秒"
FROM SYSIBM.SYSDUMMY1;

常用格式符号速查

🎯 实战案例集合

案例1：生成当月日历

WITH DATES AS (
    SELECT DATE('2025-08-01') + (ROW_NUMBER() OVER() - 1) DAYS AS CAL_DATE
    FROM SYSIBM.SYSDUMMY1
    FETCH FIRST 31 ROWS ONLY
)
SELECT 
    CAL_DATE,
    DAYNAME(CAL_DATE) AS "星期",
    DAYOFWEEK(CAL_DATE) AS "星期码",
    CASE WHEN DAYOFWEEK(CAL_DATE) IN (1,7) THEN '周末' ELSE '工作日' END AS "类型"
FROM DATES
WHERE MONTH(CAL_DATE) = 8;

案例2：计算员工工作时长

SELECT 
    EMP_ID,
    VARCHAR_FORMAT(CLOCK_IN, 'HH:MI AM') AS "上班时间",
    VARCHAR_FORMAT(CLOCK_OUT, 'HH:MI AM') AS "下班时间",
    (CLOCK_OUT - CLOCK_IN) HOUR TO MINUTE AS "工作时长"
FROM EMPLOYEE_ATTENDANCE
WHERE DATE(CLOCK_IN) = CURRENT DATE;

案例3：季度报表统计

SELECT 
    VARCHAR_FORMAT(ORDER_DATE, 'YYYY-Q') AS "年季度",
    COUNT(*) AS "订单数",
    SUM(AMOUNT) AS "总金额"
FROM ORDERS
WHERE 
    YEAR(ORDER_DATE) = 2025 AND
    QUARTER(ORDER_DATE) IN (2,3)  -- 第二三季度
GROUP BY VARCHAR_FORMAT(ORDER_DATE, 'YYYY-Q')
ORDER BY 1;

💡 专家小贴士

1. 时区处理：DB9.7+版本支持CURRENT TIMEZONE获取时区偏移

   

   SELECT CURRENT TIMEZONE FROM SYSIBM.SYSDUMMY1;  -- 返回类似+08:00

2. 闰秒彩蛋：DB2内部处理闰秒时会自动调整为59分60秒

3. 性能优化：对日期字段使用函数会导致索引失效,应该：

   -- 不推荐（索引失效）
   SELECT * FROM ORDERS WHERE MONTH(ORDER_DATE) = 8;
   -- 推荐（能用索引）
   SELECT * FROM ORDERS WHERE ORDER_DATE BETWEEN '2025-08-01' AND '2025-08-31';

4. 千年虫警示：两位数年份处理要小心，建议始终使用四位年份

现在小王已经能优雅地处理领导的需求了：

SELECT SUM(SALES) 
FROM DAILY_REPORTS
WHERE REPORT_DATE BETWEEN 
    CURRENT DATE - (DAYOFWEEK(CURRENT DATE) + 3 DAYS - 7 DAYS  -- 上周三
    AND 
    CURRENT DATE - (DAYOFWEEK(CURRENT DATE) + 1) DAYS + 1 DAYS  -- 本周二

下次当你面对时间数据手足无措时，记得DB2这些时间函数就是你口袋里的时光机！⏰ 掌握它们,让时间成为你的得力助手而不是绊脚石~