达梦数据库时间函数全面解析与统计应用

一、核心时间函数详解

1.获取当前时间

-- 系统当前日期时间 SELECT SYSDATE FROM DUAL; -- 2024-01-15 14:30:45 SELECT SYSTIMESTAMP FROM DUAL; -- 2024-01-15 14:30:45.123456 SELECT CURRENT_DATE FROM DUAL; -- 当前日期 SELECT CURRENT_TIMESTAMP FROM DUAL; -- 带时区的时间戳

2.时间提取函数

-- EXTRACT 提取特定部分 SELECT EXTRACT(YEAR FROM SYSDATE) AS 年, EXTRACT(MONTH FROM SYSDATE) AS 月, EXTRACT(DAY FROM SYSDATE) AS 日, EXTRACT(HOUR FROM SYSTIMESTAMP) AS 时, EXTRACT(MINUTE FROM SYSTIMESTAMP) AS 分, EXTRACT(SECOND FROM SYSTIMESTAMP) AS 秒 FROM DUAL; -- 快捷提取函数 SELECT TO_CHAR(SYSDATE, 'YYYY') AS 年, TO_CHAR(SYSDATE, 'MM') AS 月, TO_CHAR(SYSDATE, 'DD') AS 日, TO_CHAR(SYSDATE, 'HH24') AS 时, TO_CHAR(SYSDATE, 'MI') AS 分, TO_CHAR(SYSDATE, 'SS') AS 秒 FROM DUAL;

3.时间计算函数

-- 加减时间 SELECT SYSDATE AS 当前时间, SYSDATE + 7 AS 7天后, -- 加天数 ADD_DAYS(SYSDATE, 7) AS 加7天, -- 专用函数 ADD_MONTHS(SYSDATE, 3) AS 3个月后, -- 月份加减 ADD_MONTHS(SYSDATE, -2) AS 2个月前, ADD_SECONDS(SYSDATE, 3600) AS 1小时后, -- 秒数加减 ADD_MINUTES(SYSDATE, 30) AS 30分钟后 -- 分钟加减 FROM DUAL;

4.时间差计算

-- 计算差值 SELECT MONTHS_BETWEEN('2024-06-01', '2024-01-01') AS 月份差, -- 5 DATEDIFF(DAY, '2024-01-01', '2024-01-15') AS 天数差, -- 14 DATEDIFF(HOUR, '2024-01-15 08:00', '2024-01-15 17:30') AS 小时差, -- 9 NUMTODSINTERVAL(END_TIME - START_TIME, 'DAY') AS 间隔 -- 时间间隔 FROM DUAL;

二、时间统计实战场景

1.按时间段统计

-- 按日统计销售额 SELECT TO_CHAR(order_time, 'YYYY-MM-DD') AS 日期, COUNT(*) AS 订单数, SUM(amount) AS 总金额 FROM orders WHERE order_time >= TRUNC(SYSDATE, 'DD') - 30 -- 最近30天 GROUP BY TO_CHAR(order_time, 'YYYY-MM-DD') ORDER BY 日期 DESC; -- 按时段统计访问量（每小时） SELECT TO_CHAR(access_time, 'YYYY-MM-DD HH24') || ':00' AS 时段, COUNT(*) AS 访问次数 FROM user_access WHERE access_time >= SYSDATE - 1 -- 最近24小时 GROUP BY TO_CHAR(access_time, 'YYYY-MM-DD HH24') ORDER BY 时段;

2.环比/同比统计

-- 月环比统计 WITH monthly_stats AS ( SELECT TO_CHAR(sale_date, 'YYYY-MM') AS 月份, SUM(amount) AS 销售额 FROM sales GROUP BY TO_CHAR(sale_date, 'YYYY-MM') ) SELECT 月份, 销售额, LAG(销售额) OVER (ORDER BY 月份) AS 上月销售额, ROUND((销售额 - LAG(销售额) OVER (ORDER BY 月份)) / LAG(销售额) OVER (ORDER BY 月份) * 100, 2) AS 环比增长率 FROM monthly_stats ORDER BY 月份 DESC; -- 年同比统计 SELECT TO_CHAR(sale_date, 'YYYY') AS 年份, SUM(CASE WHEN TO_CHAR(sale_date, 'MM') = '01' THEN amount END) AS 一月销售额, SUM(CASE WHEN TO_CHAR(sale_date, 'MM') = '01' AND TO_CHAR(sale_date, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') THEN amount END) AS 今年一月, SUM(CASE WHEN TO_CHAR(sale_date, 'MM') = '01' AND TO_CHAR(sale_date, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY') - 1 THEN amount END) AS 去年一月 FROM sales GROUP BY TO_CHAR(sale_date, 'YYYY') ORDER BY 年份;

3.工作日/节假日统计

-- 计算工作日（排除周末） SELECT start_date, end_date, COUNT(*) AS 总天数, COUNT(CASE WHEN TO_CHAR(start_date + LEVEL - 1, 'D') NOT IN ('6', '7') THEN 1 END) AS 工作日数 FROM projects CONNECT BY LEVEL <= end_date - start_date + 1 GROUP BY start_date, end_date; -- 统计每月工作日 SELECT TO_CHAR(work_date, 'YYYY-MM') AS 月份, COUNT(*) AS 工作日数, SUM(work_hours) AS 总工时 FROM work_records WHERE TO_CHAR(work_date, 'D') NOT IN ('6', '7') -- 排除周六日 GROUP BY TO_CHAR(work_date, 'YYYY-MM');

4.时间段分布统计

-- 访问时间段分布 SELECT CASE WHEN TO_CHAR(access_time, 'HH24') BETWEEN '00' AND '05' THEN '00-06点' WHEN TO_CHAR(access_time, 'HH24') BETWEEN '06' AND '11' THEN '06-12点' WHEN TO_CHAR(access_time, 'HH24') BETWEEN '12' AND '17' THEN '12-18点' ELSE '18-24点' END AS 时间段, COUNT(*) AS 访问量, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS 占比 FROM user_access WHERE access_time >= TRUNC(SYSDATE) GROUP BY CASE WHEN TO_CHAR(access_time, 'HH24') BETWEEN '00' AND '05' THEN '00-06点' WHEN TO_CHAR(access_time, 'HH24') BETWEEN '06' AND '11' THEN '06-12点' WHEN TO_CHAR(access_time, 'HH24') BETWEEN '12' AND '17' THEN '12-18点' ELSE '18-24点' END ORDER BY 访问量 DESC;

三、高级时间处理技巧

1.时间窗口统计

-- 滑动窗口统计（最近7天移动平均） SELECT stat_date, daily_amount, AVG(daily_amount) OVER ( ORDER BY stat_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS 7日移动平均, SUM(daily_amount) OVER ( ORDER BY stat_date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW ) AS 最近7天累计 FROM daily_stats ORDER BY stat_date; -- 同比时间窗口 SELECT curr.日期, curr.销售额 AS 本期, prev.销售额 AS 同期, ROUND((curr.销售额 - prev.销售额) / prev.销售额 * 100, 2) AS 同比增长率 FROM ( SELECT TO_CHAR(sale_date, 'MM-DD') AS 日期, SUM(amount) AS 销售额 FROM sales WHERE TO_CHAR(sale_date, 'YYYY') = '2024' GROUP BY TO_CHAR(sale_date, 'MM-DD') ) curr LEFT JOIN ( SELECT TO_CHAR(sale_date, 'MM-DD') AS 日期, SUM(amount) AS 销售额 FROM sales WHERE TO_CHAR(sale_date, 'YYYY') = '2023' GROUP BY TO_CHAR(sale_date, 'MM-DD') ) prev ON curr.日期 = prev.日期;

2.时间序列补全

-- 补全缺失的日期数据 WITH date_range AS ( SELECT TRUNC(SYSDATE) - LEVEL + 1 AS stat_date FROM DUAL CONNECT BY LEVEL <= 30 -- 最近30天 ) SELECT dr.stat_date AS 日期, NVL(ds.amount, 0) AS 金额, NVL(ds.order_count, 0) AS 订单数 FROM date_range dr LEFT JOIN daily_stats ds ON dr.stat_date = ds.stat_date ORDER BY dr.stat_date DESC;

3.性能优化建议

-- 1. 为时间字段创建索引 CREATE INDEX idx_orders_time ON orders(order_time); CREATE INDEX idx_sales_date ON sales(sale_date); -- 2. 使用函数索引优化时间查询 CREATE INDEX idx_monthly_stat ON sales(TO_CHAR(sale_date, 'YYYY-MM')); -- 3. 避免在WHERE条件中使用函数（优化前） SELECT * FROM orders WHERE TO_CHAR(order_time, 'YYYY-MM-DD') = '2024-01-15'; -- 优化后（使用范围查询） SELECT * FROM orders WHERE order_time >= TO_DATE('2024-01-15', 'YYYY-MM-DD') AND order_time < TO_DATE('2024-01-16', 'YYYY-MM-DD');

四、实用时间模板函数

-- 获取月初/月末 SELECT TRUNC(SYSDATE, 'MM') AS 本月第一天, LAST_DAY(SYSDATE) AS 本月最后一天, TRUNC(SYSDATE, 'YYYY') AS 年初, ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1 AS 年末 FROM DUAL; -- 计算年龄 SELECT birthday, FLOOR(MONTHS_BETWEEN(SYSDATE, birthday) / 12) AS 年龄, TRUNC(MONTHS_BETWEEN(SYSDATE, birthday)) AS 月龄 FROM employees; -- 工作时间计算 SELECT start_time, end_time, ROUND((end_time - start_time) * 24, 2) AS 工作小时数, ROUND((end_time - start_time) * 24 * 60, 0) AS 工作分钟数 FROM work_logs;

这些是达梦数据库时间函数的主要应用场景和统计技巧。根据您的具体需求，可以选择合适的函数进行时间数据处理和统计分析。