MySQL 常用日期时间与数值函数实战指南
掌握 MySQL 数据处理离不开对内置函数的灵活运用。本文系统梳理了 time_to_sec、date_add、from_unixtime 等核心日期函数，以及 abs、floor、round 等数值计算函数，提供详细的参数说明与查询案例，助力提升 SQL 编写效率。
日期与时间转换函数
在进行数据清洗和报表生成时，经常需要在不同格式之间转换时间数据。以下是处理秒数、天数及字符串互转的常用函数。
第1步：处理秒与时间的相互转换
time_to_sec() 将 HH:MM:SS 格式的时间转换为总秒数；sec_to_time() 则将秒数还原为时间格式。
-- 将 '01:01:01' 转换为秒数 (1*3600 + 1*60 + 1 = 3661)
SELECT time_to_sec('01:01:01');
-- 结果: 3661-- 将 3661 秒还原为时间
SELECT sec_to_time(3661);
-- 结果: 01:01:01第2步：日期与天数的相互转换
to_days() 计算自公元 0000-00-00 以来的天数，注意无效日期可能返回 NULL。from_days() 执行反向操作。
-- 计算天数，无效日期返回 NULL
SELECT to_days('0000-00-00'); -- NULL
SELECT to_days('0001-01-01'); -- 366-- 天数转日期
SELECT from_days(366);
-- 结果: 0001-01-01第3步：字符串与日期的互相转换
使用 str_to_date() 按指定格式解析字符串为日期对象，date_format() 和 time_format() 则将日期对象转为特定格式的字符串。
-- 字符串转日期
SELECT str_to_date('2013-01-01 01:21:01', '%Y-%m-%d %H:%i:%s');-- 日期转字符串（去除分隔符）
SELECT date_format('2013-01-01 01:21:01', '%Y%m%d %H%i%s');
-- 结果: 20130101 012101
-- 时间转字符串
SELECT time_format('01:21:01', '%H%i%s');
-- 结果: 012101日期提取与当前时间获取
从完整的 DateTime 中提取年、月、日或周信息，以及获取系统当前时刻，是筛选数据的常见需求。
第4步：提取日期组件信息
利用专用函数可快速获取特定部分，如 year(), month(), day(), weekday() 等。
-- 提取日期部分
SELECT date(now());
-- 结果: 2013-05-16-- 提取星期索引 (0=星期一, 6=星期天)
SELECT weekday(now());
-- 例如: 3 (星期四)-- 提取一年中的第几周 (第二个参数决定起始日为周日还是周一)
SELECT week(now(), 0); -- 0表示从周日开始
SELECT week(now(), 1); -- 1表示从周一开始-- 提取季度、月份名、星期名等
SELECT quarter(now()); -- 第2季度
SELECT monthname(now()); -- May
SELECT dayname(now()); -- Thursday第5步：获取当前日期与时间
区分 now() 和 sysdate() 至关重要。前者在事务开始时固定一次，后者实时获取调用时的时间。
-- 比较 now() 和 sysdate() 的区别
SELECT sysdate(), sleep(2), sysdate();
-- 两次 sysdate() 结果相差约2秒
SELECT now(), sleep(2), now();
-- 两次 now() 结果相同第6步：Unix 时间戳转换
unix_timestamp() 和 from_unixtime() 用于处理跨系统兼容的时间格式。
-- 获取当前 Unix 时间戳
SELECT unix_timestamp();
-- 将具体时间的字符串转为时间戳
SELECT unix_timestamp('2013-05-16 01:01:01');
-- 时间戳转标准日期格式
SELECT from_unixtime(1368637261);
-- 自定义格式化输出
SELECT from_unixtime(1368637261, '%Y-%m-%d %h:%i:%s');日期加减与间隔计算
进行排期、有效期判断或日志分析时，需要基于基准时间进行加减运算。
第7步：使用 DATE_ADD / DATE_SUB 进行加减
语法：DATE_ADD(date, INTERVAL expr type)。
-- 加1天
SELECT date_add('2013-05-16', INTERVAL 1 DAY);
-- 加1小时30分 (使用 HOUR_MINUTE 类型)
SELECT date_add('2013-05-16 01:01:01', INTERVAL '1:30' HOUR_MINUTE);
-- 减1个月
SELECT date_sub('2013-05-16', INTERVAL 1 MONTH);常用 TYPE 参数：
- SECOND (秒), MINUTE (分), HOUR (时), DAY (天), MONTH (月), YEAR (年)
- 复合类型: 'M:S' MINUTE_SECOND, 'H:M:S' HOUR_SECOND, 'D H:M:S' DAY_SECOND
第8步：计算日期差值
DATEDIFF(expr1, expr2) 返回两个日期之间的天数（expr1 - expr2），仅对比日期部分，忽略时分秒。
-- 计算两个日期的天数差
SELECT DATEDIFF('2013-05-17', '2013-05-16');
-- 结果: 1
SELECT DATEDIFF('2013-05-16 06:00:00', '2013-05-17 01:00:00');
-- 结果: -1 (因为只比日期: 16号 比 17号)常用日期格式代码速查
在使用 date_format 或 str_to_date 时，以下为高频格式标识符：
| 代码 | 含义 | 示例 |
|---|---|---|
| %Y | 四位年份 | 2013 |
| %y | 两位年份 | 13 |
| %m | 月份 (01-12) | 05 |
| %c | 月份 (1-12) | 5 |
| %d | 日期 (00-31) | 16 |
| %H | 小时 (00-23) | 17 |
| %h/%I | 小时 (01-12) | 05 |
| %i | 分钟 (00-59) | 31 |
| %s/%S | 秒 (00-59) | 34 |
数值处理函数
除了时间处理，MySQL 也提供了基础的数学运算功能，常用于数据标准化或简单逻辑计算。
第9步：基础数学运算
ABS(X) 返回绝对值。
FLOOR(X) 返回不大于 X 的最大整数（向下取整）。
MOD(N, M) 返回 N 除以 M 的余数。
SELECT ABS(-5); -- 5
SELECT FLOOR(3.9); -- 3
SELECT MOD(10, 3); -- 1第10步：随机数与四舍五入
RAND() 返回 0 到 1 之间的随机浮点数。带参数 RAND(seed) 可产生可复现的序列。
ROUND(X) 四舍五入到最近整数。
ROUND(X, D) 保留 D 位小数（D可为负数，向左取整到十/百位）。
SELECT RAND(); -- 类似 0.843...
SELECT ROUND(2.432, 2); -- 2.43
SELECT ROUND(15.5, -1); -- 20 (向十位四舍五入)总结与注意事项
在实际 SQL 编写中，处理时间字段时需留意隐式转换问题。例如直接使用字符串比较日期范围可能因格式不一致导致索引失效或逻辑错误，建议优先使用 BETWEEN ... AND ... 配合标准时间格式，或在 WHERE 子句中保持两侧数据类型一致。对于复杂的周期性统计，结合 WEEK()、MONTH() 等提取函数并结合 GROUP BY 能有效简化代码结构。
本站声明
本文内容仅供参考，不代表本站观点。转载或引用请注明出处。如发现内容问题，请通过联系我们页面反馈。
