MySQL 日期时间类型选择与常用函数全解

一、MySQL 日期与时间数据类型

在创建数据库表时，合理选择日期和时间字段类型至关重要。以下表格列出了常见的四种类型及其特性：

1. DATETIME：占用 8 bytes，格式为 YYYY-MM-DD HH:MM:SS，范围从 1000-01-01 至 9999-12-31。此类型不受服务器时区影响，直接保存传入的绝对时间。
2. TIMESTAMP：占用 4 bytes，同样显示为 YYYY-MM-DD HH:MM:SS，但有效范围仅为 1970-01-01 00:00:01 至 2038-01-19（受Unix时间戳限制）。该类型最显著的特性是默认情况下会在 INSERT 或 UPDATE 时自动以当前时间填充或更新。
3. DATE：占用 3 bytes，仅包含日期部分 YYYY-MM-DD，范围同 DATETIME。
4. YEAR：占用 1 byte，存储年份，范围在 1901 到 2155 之间。

第1步：DATETIME 与 TIMESTAMP 的选择策略

两者各有优劣，需根据业务需求权衡：

• 存储空间：TIMESTAMP 仅需 DATETIME 一半的空间，适合对存储敏感的场景。
• 自动更新能力：TIMESTAMP 具有内置的自动更新功能，适合记录“最后修改时间”等无需手动干预的字段。
• 时区处理：TIMESTAMP 会将存储的时间转换为服务器本地时区返回，这可能导致跨时区查询时的误解；DATETIME 则始终忠实于原始输入值。
• 有效期限制：若业务预期跨越 2038 年，必须弃用 TIMESTAMP 以防止溢出错误。

以下是利用 DEFAULT CURRENT_TIMESTAMP 特性自动处理时间的建表示例：

CREATE TABLE t8 (
  `id1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id2` DATETIME DEFAULT NULL
);

第2步：TIME 类型与时间差计算

TIME 类型占用 3 bytes，用于表示时间间隔或一天中的具体时间，格式为 HH:MM:SS。其特殊之处在于支持负数，范围为 -838:59:59 到 838:59:59。这种设计专门为了满足两个时间点相减的需求。

使用 timediff() 函数可以计算两个参数的差值，但要求两个参数类型必须一致（同为 TIME 或同为 DATETIME/TIMESTAMP）：

-- 计算正时间差
SELECT TIMEDIFF('2000-01-31 23:59:59', '2000-01-01 00:00:00');

-- 计算负时间差
SELECT TIMEDIFF('2000-01-01 00:00:00', '2000-01-31 23:59:59');

注意：目前标准 MySQL 版本（含 5.X）的日期时间函数精度通常只支持到秒，不支持毫秒或微秒级别的计算和生成。

二、基础日期时间获取函数

第3步：获取当前时间与 UTC 时间

最核心的时间获取函数是 now()。它与 current_timestamp() 等同效，推荐使用简短的 now()。此外，还有一个特殊的 sysdate() 函数，它在语句执行过程中动态获取当前系统时间，而非执行开始时固定，适用于需要高精度间隔控制的场景：

-- now() 在整个语句中保持不变
SELECT NOW(), SLEEP(3), NOW(); 

-- sysdate() 会捕捉到延迟后的时间变化
SELECT SYSDATE(), SLEEP(3), SYSDATE();

对于多时区应用，建议使用 utc_date()、utc_time() 和 utc_timestamp()。中国位于东八区，因此本地时间 = UTC + 8小时。

三、Unix 时间戳转换与时间增减

第4步：Unix 时间戳互换

在编程交互中，常需在 MySQL 时间与 Unix 时间戳（自 1970-01-01 以来的秒数）间转换：

• unix_timestamp()：将当前时间转换为 Unix 时间戳。
• from_unixtime(unix_timestamp, format)：将 Unix 时间戳转回日期格式，可选指定输出格式。

示例：

-- 转为 Unix 时间戳
SELECT UNIX_TIMESTAMP('2008-08-08 12:30:00'); -- 结果约为 1218169800

-- 逆转换并自定义格式
SELECT FROM_UNIXTIME(1218169800, '%Y-%m-%d %H:%i:%s');

第5步：时间加减运算

推荐使用 date_add() 和 date_sub() 来进行日期的加减法，替代旧版的 adddate() 和 subdate()。

timestampadd() 专门用于按单位递增或递减，而 timestampdiff() 用于计算两个时间之间的差值（以指定单位返回整数结果，比仅能返回天数的 datediff() 更强大）：

-- 增加一天
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

-- 计算两个时间相差的小时数
SELECT TIMESTAMPDIFF(HOUR, '2008-08-08 00:00:00', '2008-08-08 12:00:00');

四、高级提取与格式化函数

第6步：日期信息提取

使用 year()、month()、day()、week() 等函数可直接抽取日期的各个组成部分。如果需要更复杂的组合提取（如连续提取日时分），可使用 extract()：

SET @dt = '2008-09-10 07:15:30.123456';
SELECT EXTRACT(DAY_SECOND FROM @dt);  -- 结果为 10071530（从 day 一直到 second）

第7步：字符串与日期互转

在处理非标准格式的数据录入时，转换函数非常关键：

• str_to_date(str, format)：将符合特定格式的字符串解析为日期对象。例如将 '08/09/2008' 解析为标准日期。
• date_format(date, format)：将日期对象格式化为字符串，常用于报表展示。

示例：

-- 杂乱字符串转标准日期
SELECT STR_TO_DATE('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s');

-- 日期转可读字符
SELECT DATE_FORMAT('2008-08-08 22:23:01', '%W %M %Y');

五、时区调整函数

如果系统涉及跨国时区，可使用 convert_tz(dt, from_tz, to_tz) 进行显式转换：

-- 将北京时间(+08:00)转换为协调世界时(+00:00)
SELECT CONVERT_TZ('2008-08-08 12:00:00', '+08:00', '+00:00');

当然，这也等同于利用 DATE_ADD 减去对应的小时数，但在逻辑表达上更为直观。

以上就是 MySQL 日期时间数据类型使用总结及常见函数的详细内容，更多关于 MySQL 日期处理技巧的资料请关注本站其它相关文章！