时间日期类型
MySQL中主要支持以下几种时间日期类型：
-
DATE - 日期类型
- 格式：'YYYY-MM-DD'
- 范围：'1000-01-01' 到 '9999-12-31'
- 示例：'2023-05-20'
-
TIME - 时间类型
- 格式：'HH:MM:SS'
- 范围：'-838:59:59' 到 '838:59:59'
- 示例：'15:30:00' 或 '02:45:30.123456'（支持微秒）
-
DATETIME - 日期时间类型
- 格式：'YYYY-MM-DD HH:MM:SS'
- 范围：'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
- 存储方式：按字面值存储，不进行时区转换
- 示例：'2023-05-20 15:30:00'
- 适合存储固定时间点（如生日、历史事件）
- 不自动更新
- 占用 8 字节 存储空间
-
TIMESTAMP - 时间戳类型
- 格式：'YYYY-MM-DD HH:MM:SS'
- 范围：
1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC（受 32 位限制） - 存储方式：转换为 UTC 存储，检索时转回当前时区
- 适合记录数据变更时间（如
created_at、updated_at） - 可自动初始化/更新（配合
DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP） - 占用 4 字节 存储空间
- 受时区影响（存储和检索时会根据服务器时区转换）
-
YEAR - 年份类型
- 格式：YYYY
- 范围：1901 到 2155
- 示例：'2023'
DATETIME VS TIMESTAMP
| 特性 | DATETIME | TIMESTAMP |
|---|---|---|
| 范围 | 1000-9999 年 | 1970-2038 年（UTC） |
| 时区处理 | 按字面值存储 | 存储时转 UTC，检索时转回本地时区 |
| 自动更新 | 不支持 | 支持（需显式定义） |
| 存储空间 | 8 字节 | 4 字节 |
| 典型用途 | 固定时间（如生日） | 记录时间（如日志、更新时间戳） |
使用建议
- 需要记录 事件发生的具体时间（如订单时间）且不关心时区 → DATETIME
- 需要 自动记录数据插入/更新时间 或处理时区敏感数据 → TIMESTAMP
- 注意 TIMESTAMP 的 2038 年问题（未来 MySQL 可能扩展为 64 位）
日期增减
日期加：DATE_ADD
基本语法
DATE_ADD(date, INTERVAL expr unit)
功能
在指定日期/时间上添加一个时间间隔
参数说明
date：要处理的日期/时间值（可以是DATE, DATETIME或TIMESTAMP类型）expr：要添加的间隔数值（可以是正数或负数）unit：时间单位（见下方支持的单位）
支持的时间单位
MICROSECOND | SECOND | MINUTE | HOUR | DAY
| WEEK | MONTH | QUARTER | YEAR
| SECOND_MICROSECOND | MINUTE_MICROSECOND
| MINUTE_SECOND | HOUR_MICROSECOND
| HOUR_SECOND | HOUR_MINUTE
| DAY_MICROSECOND | DAY_SECOND
| DAY_MINUTE | DAY_HOUR
| YEAR_MONTH
使用示例
-- 加1天
SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
-- 结果: 2023-01-02-- 加1个月
SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH);
-- 结果: 2023-02-28 (自动处理月末)-- 加1年
SELECT DATE_ADD('2020-02-29', INTERVAL 1 YEAR);
-- 结果: 2021-02-28 (闰年自动调整)-- 加3小时30分钟
SELECT DATE_ADD('2023-01-01 12:00:00', INTERVAL '3:30' HOUR_MINUTE);
-- 结果: 2023-01-01 15:30:00-- 使用负数表示减法
SELECT DATE_ADD('2023-01-01', INTERVAL -1 DAY);
-- 结果: 2022-12-31
日期减：DATE_SUB
基本语法
DATE_SUB(date, INTERVAL expr unit)
功能
与DATE_ADD相反，从指定日期/时间减去一个时间间隔
使用示例
-- 减1天
SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY);
-- 结果: 2022-12-31-- 减1个月
SELECT DATE_SUB('2023-03-31', INTERVAL 1 MONTH);
-- 结果: 2023-02-28-- 等同于DATE_ADD使用负数
SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY) = DATE_ADD('2023-01-01', INTERVAL -1 DAY);
-- 结果: 1(TRUE)
简写语法
MySQL还提供更简洁的日期加减写法：
-- 加1天
SELECT '2023-01-01' + INTERVAL 1 DAY;-- 减1个月
SELECT '2023-01-01' - INTERVAL 1 MONTH;
实际应用场景
场景1：计算到期日
-- 贷款30天后到期
SELECT loan_date, DATE_ADD(loan_date, INTERVAL 30 DAY) AS due_date
FROM loans;
场景2：查找最近7天的记录
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);
场景3：处理订阅续期
-- 订阅续期1年
UPDATE subscriptions
SET end_date = DATE_ADD(end_date, INTERVAL 1 YEAR)
WHERE user_id = 1001;
注意事项
-
自动调整无效日期：
SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH); -- 返回2023-02-28 -
闰年处理：
SELECT DATE_ADD('2020-02-29', INTERVAL 1 YEAR); -- 返回2021-02-28 -
时间溢出处理：
SELECT DATE_ADD('2023-01-01 23:59:59', INTERVAL 1 SECOND); -- 返回2023-01-02 00:00:00 -
性能考虑：在WHERE条件中对列使用这些函数会导致索引失效
与其他日期函数的对比
| 需求 | 推荐函数 | 示例 |
|---|---|---|
| 计算未来/过去特定时间点 | DATE_ADD/DATE_SUB | DATE_ADD(now(), INTERVAL 1 HOUR) |
| 计算两个日期的差值 | DATEDIFF/TIMESTAMPDIFF | DATEDIFF(end_date, start_date) |
| 提取日期部分 | DATE_FORMAT/EXTRACT | EXTRACT(YEAR FROM date) |
| 获取当前日期/时间 | NOW/CURDATE | SELECT CURDATE(), NOW() |
时间/日期差
时间差：TIMESTAMPDIFF
TIMESTAMPDIFF 是 MySQL/MariaDB 中用于计算两个时间戳之间差异的函数。
基本语法
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
unit: 时间单位（YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND 等）datetime_expr1: 起始时间datetime_expr2: 结束时间
时间单位选项
| 单位 | 说明 |
|---|---|
| MICROSECOND | 微秒 |
| SECOND | 秒 |
| MINUTE | 分钟 |
| HOUR | 小时 |
| DAY | 天 |
| WEEK | 周 |
| MONTH | 月 |
| QUARTER | 季度 |
| YEAR | 年 |
使用示例
-
计算小时差
SELECT TIMESTAMPDIFF(HOUR, '2022-03-22 08:00:00', '2022-03-22 17:30:00'); -- 返回 9（只计算完整的小时数，忽略分钟部分） -
计算天数差
SELECT TIMESTAMPDIFF(DAY, '2022-03-01', '2022-03-15'); -- 返回 14 -
计算月数差
SELECT TIMESTAMPDIFF(MONTH, '2022-01-15', '2022-05-20'); -- 返回 4（不考虑具体的天数）
重要特性
-
返回值总是整数：函数返回的是两个时间之间的
完整单位数，不包含小数部分 -
参数顺序敏感：
datetime_expr2减去datetime_expr1，如果datetime_expr1更大，结果为负数 -
自动处理日期格式：函数会自动识别标准日期时间格式
-
跨年/月计算：能正确处理跨年、跨月的时间差计算
高级用法
-
计算年龄
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users; -
计算服务时长（精确到月）
SELECT employee_name,TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_of_service FROM employees; -
组合使用
SELECT CONCAT(TIMESTAMPDIFF(YEAR, '2000-01-01', '2023-05-15'), '年',TIMESTAMPDIFF(MONTH, '2000-01-01', '2023-05-15') % 12, '个月') AS time_diff;
注意事项
-
对于
MONTH和YEAR单位，函数只比较月份和年份部分，不考虑具体的天数 -
如果需要更精确的时间差（包含小数），可以考虑使用
TIMESTAMPDIFF结合其他函数，或者使用UNIX_TIMESTAMP计算秒数差再转换 -
在旧版 MySQL 中可能不支持某些时间单位（如 MICROSECOND）
案例
统计各岗位员工平均工作时长
时间差：UNIX_TIMESTAMP
UNIX_TIMESTAMP 是 SQL 中常用的时间函数，主要用于获取或转换 Unix 时间戳（从1970年1月1日00:00:00 UTC到当前时间的秒数）。
计算时间差
SELECT (UNIX_TIMESTAMP('2022-03-22 17:00:00') -
UNIX_TIMESTAMP('2022-03-22 08:00:00')) / 3600
AS hours_diff;
返回当前时间的Unix时间戳
SELECT UNIX_TIMESTAMP(); 日期时间→时间戳
SELECT UNIX_TIMESTAMP('2023-10-01 12:00:00');时间戳→日期时间
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); 日期差：DATEDIFF
DATEDIFF()是MySQL中用于计算两个日期之间天数差的函数。
DATEDIFF(date1, date2)date1和date2是日期或日期时间表达式- 函数返回
date1减去date2的天数差
示例
计算两个日期之间的天数差
SELECT DATEDIFF('2023-12-31', '2023-01-01'); -- 返回 364计算当前日期与特定日期的天数差
SELECT DATEDIFF(CURDATE(), '2023-01-01');在表查询中使用
SELECT order_id,
DATEDIFF(shipped_date, order_date) AS processing_days
FROM orders;注意事项
- 时间部分会被忽略，只计算日期部分
- 如果date1早于date2，结果为负数
- 参数可以是DATE、DATETIME或TIMESTAMP类型
- 如果任一参数为NULL，结果也为NULL
- TIMESTAMPDIFF()可以计算更精确的时间差（年、月、日、小时等），DATEDIFF()只返回天数差
日期格式化
DATE_FORMAT
在 MySQL 中，DATE_FORMAT() 函数用于将日期或时间值格式化为指定的字符串格式。
正确语法
DATE_FORMAT(date, format)
常用格式说明符
| 说明符 | 描述 |
|---|---|
| %Y | 四位数的年份（例如：2023） |
| %y | 两位数的年份（例如：23） |
| %m | 两位数的月份（01-12） |
| %d | 两位数的日期（01-31） |
| %H | 24小时制的小时（00-23） |
| %i | 分钟（00-59） |
| %s | 秒（00-59） |
示例用法
- 获取四位数的年份：
SELECT DATE_FORMAT(created_at, '%Y') FROM your_table;
- 获取完整的日期时间格式：
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') FROM your_table;
- 获取月份和年份：
SELECT DATE_FORMAT(created_at, '%M %Y') FROM your_table;
-- 结果示例："May 2023"
- 在你的例子中，如果要提取年份：
SELECT DATE_FORMAT(created_at, '%Y') AS year FROM your_table;
注意事项
- MySQL 的
DATE_FORMAT()函数使用百分号 (%)作为格式说明符的前缀 - 函数名是
DATE_FORMAT而不是dateformt（注意大小写不敏感但拼写要正确） - 第一个参数是日期/时间列或表达式，第二个参数是格式字符串
day()、month()、year()
selectdate_format (date, "%d") as day,count(*) as question_cnt
fromquestion_practice_detail
wheredate_format (date, "%m") = 8and date_format (date, "%y") = 21
group by day
用day、month、year简化
selectday (date) as day,count(*) as question_cnt
fromquestion_practice_detail
wheremonth (date) = 8and year (date) = 2021
group byday
（1）like语法：date like “2021-08%”
（2）year、month函数：year(date)=‘2021’ and month(date)=‘08’；
（3）date_format函数：date_format(date, ‘%Y-%m’)=‘2021-08’;
其他函数
last_day()
last_day()返回参数日期的最后一天
SELECT*,LAST_DAY( end_date ) as `LAST_DAY`
FROMjob_history;
报错的解决方案)
