MySQL时间戳与日期格式转换技巧

MySQL中的时间数据处理经常涉及时间戳、日期时间以及字符串之间的相互转换。实际开发中，日志记录、订单时间、用户注册时间、数据统计等场景都需要进行时间格式处理。掌握UNIX_TIMESTAMP()、FROM_UNIXTIME()、DATE_FORMAT()、STR_TO_DATE()等函数，可以有效解决MySQL时间戳与日期格式转换问题。

一、MySQL常见时间类型

MySQL中常见的时间字段类型主要包括：

• DATE：只保存日期，例如2026-10-08

• TIME：只保存时间，例如18:30:00

• DATETIME：保存日期和时间，例如2026-10-08 18:30:00

• TIMESTAMP：时间戳类型，也保存日期和时间，但在时区处理方面与DATETIME存在区别

• Unix时间戳：通常指从1970-01-01 00:00:00 UTC开始计算的秒数或毫秒数

需要特别注意，Unix时间戳本质上是一个整数，而MySQL的TIMESTAMP是数据库中的时间类型，两者不能简单地视为同一个概念。

二、日期时间转换为Unix时间戳

1. 使用UNIX_TIMESTAMP()

如果需要将MySQL日期时间转换成Unix时间戳，可以使用UNIX_TIMESTAMP()。

SQL
SELECT UNIX_TIMESTAMP('2026-10-08 18:30:00');

返回结果类似：

1791455400

如果直接传入DATETIME字段，也可以进行转换：

SQL
SELECT UNIX_TIMESTAMP(create_time) AS timestamp_value
FROM user;

这种方式非常适合接口数据处理。例如数据库保存的是DATETIME，而前端或第三方接口要求传递Unix时间戳，就可以直接完成转换。

2. 获取当前时间的Unix时间戳

不传参数时，UNIX_TIMESTAMP()会返回当前时间对应的Unix时间戳：

SQL
SELECT UNIX_TIMESTAMP();

也可以结合NOW()使用：

SQL
SELECT UNIX_TIMESTAMP(NOW());

两种写法通常可以得到对应的当前Unix时间。

三、Unix时间戳转换为日期时间

与UNIX_TIMESTAMP()相对应的是FROM_UNIXTIME()。

SQL
SELECT FROM_UNIXTIME(1791455400);

结果通常为：

2026-10-08 18:30:00

如果数据库中保存的是秒级时间戳，可以直接转换：

SQL
SELECT FROM_UNIXTIME(timestamp_column)
FROM orders;

1. 时间戳直接格式化

FROM_UNIXTIME()还支持格式参数，可以在转换时间戳的同时输出指定格式。

SQL
SELECT FROM_UNIXTIME(1791455400, '%Y-%m-%d');

结果：

2026-10-08

如果需要完整的年月日时分秒：

SQL
SELECT FROM_UNIXTIME(1791455400, '%Y-%m-%d %H:%i:%s');

常见格式符包括：

需要注意，MySQL中分钟使用%i，并不是很多编程语言中常见的%m。

四、DATETIME格式化为指定日期字符串

如果数据库字段本身就是DATETIME，通常使用DATE_FORMAT()进行格式化。

例如：

SQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

得到：

2026-10-08

格式化完整时间：

SQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

还可以用于数据库字段：

SQL
SELECT DATE_FORMAT(create_time, '%Y年%m月%d日 %H:%i:%s') AS formatted_time
FROM orders;

这样可以直接将数据库中的时间转换成适合页面展示的字符串。

五、字符串转换为日期时间

如果接收到的时间是字符串，而不是标准的MySQL日期格式，可以使用STR_TO_DATE()。

例如：

SQL
SELECT STR_TO_DATE('2026/10/08 18:30:00', '%Y/%m/%d %H:%i:%s');

返回：

2026-10-08 18:30:00

对于只有日期的字符串：

SQL
SELECT STR_TO_DATE('2026-10-08', '%Y-%m-%d');

也可以处理中文日期格式，只要格式字符串与输入内容对应即可。

例如：

SQL
SELECT STR_TO_DATE('2026年10月08日', '%Y年%m月%d日');

六、日期字符串转换为Unix时间戳

如果原始数据是字符串，可以先使用STR_TO_DATE()转换，再通过UNIX_TIMESTAMP()得到时间戳。

SQL
SELECT UNIX_TIMESTAMP(
    STR_TO_DATE('2026-10-08 18:30:00', '%Y-%m-%d %H:%i:%s')
);

这种组合方式适用于日志导入、CSV数据清洗以及接口数据转换等场景。

处理表中的字符串字段时，可以这样写：

SQL
SELECT UNIX_TIMESTAMP(
    STR_TO_DATE(time_text, '%Y-%m-%d %H:%i:%s')
) AS timestamp_value
FROM log_table;

七、毫秒时间戳如何转换

实际开发中经常会遇到13位时间戳。例如：

1791455400000

这种通常是毫秒级Unix时间戳，而MySQL的FROM_UNIXTIME()通常按照秒级时间戳处理。

因此不能直接写：

SQL
SELECT FROM_UNIXTIME(1791455400000);

正确做法是先除以1000：

SQL
SELECT FROM_UNIXTIME(1791455400000 / 1000);

如果字段是整数类型，也可以：

SQL
SELECT FROM_UNIXTIME(timestamp_ms / 1000)
FROM log_table;

如果需要保留毫秒部分，则需要结合小数秒处理。例如：

SQL
SELECT FROM_UNIXTIME(timestamp_ms / 1000.0);

具体显示精度还需要根据字段类型和应用需求处理。

判断时间戳单位时，可以简单参考：

• 10位左右：通常是秒

• 13位左右：通常是毫秒

• 更高位数：可能是微秒或其他自定义时间表示

不能只依靠位数判断，最好结合数据来源的接口文档确认。

八、Unix时间戳转换为日期并指定格式

如果既要完成时间戳转换，又需要控制最终显示格式，可以直接组合函数：

SQL
SELECT DATE_FORMAT(
    FROM_UNIXTIME(1791455400),
    '%Y-%m-%d %H:%i:%s'
);

实际查询中也很常见：

SQL
SELECT
    id,
    DATE_FORMAT(
        FROM_UNIXTIME(create_timestamp),
        '%Y-%m-%d %H:%i:%s'
    ) AS create_time
FROM orders;

这样可以将数据库中的Unix时间戳直接转换为可读的日期时间字符串。

九、日期与时间字段的拆分

MySQL还提供了多个函数，用于从日期时间中提取指定部分。

获取日期：

SQL
SELECT DATE(create_time)
FROM orders;

获取时间：

SQL
SELECT TIME(create_time)
FROM orders;

获取年份：

SQL
SELECT YEAR(create_time)
FROM orders;

获取月份：

SQL
SELECT MONTH(create_time)
FROM orders;

获取日期：

SQL
SELECT DAY(create_time)
FROM orders;

例如统计某个年份的数据：

SQL
SELECT COUNT(*)
FROM orders
WHERE YEAR(create_time) = 2026;

不过在数据量较大的表中，这种写法可能影响索引使用。更推荐使用范围条件：

SQL
SELECT COUNT(*)
FROM orders
WHERE create_time >= '2026-01-01 00:00:00'
  AND create_time < '2027-01-01 00:00:00';

对于大表查询，这种写法通常更有利于利用时间字段上的索引。

十、时间戳与日期格式转换的常见组合

实际开发中可以根据转换方向选择对应函数。

日期时间转时间戳

SQL
SELECT UNIX_TIMESTAMP('2026-10-08 18:30:00');

时间戳转日期时间

SQL
SELECT FROM_UNIXTIME(1791455400);

日期时间格式化

SQL
SELECT DATE_FORMAT(
    '2026-10-08 18:30:00',
    '%Y/%m/%d %H:%i:%s'
);

字符串转日期

SQL
SELECT STR_TO_DATE(
    '2026/10/08 18:30:00',
    '%Y/%m/%d %H:%i:%s'
);

字符串转时间戳

SQL
SELECT UNIX_TIMESTAMP(
    STR_TO_DATE(
        '2026/10/08 18:30:00',
        '%Y/%m/%d %H:%i:%s'
    )
);

毫秒时间戳转日期时间

SQL
SELECT FROM_UNIXTIME(1791455400000 / 1000);

掌握这些组合后，大多数MySQL时间戳转换需求都可以直接解决。

十一、MySQL时间戳转换中的时区问题

时间转换过程中最容易被忽略的问题之一就是时区。

Unix时间戳表示的是一个绝对时间点，而FROM_UNIXTIME()、UNIX_TIMESTAMP()等函数的结果会受到MySQL当前会话时区等设置影响。

可以查看当前会话时区：

SQL
SELECT @@session.time_zone;

查看全局时区：

SQL
SELECT @@global.time_zone;

还可以查看当前时间：

SQL
SELECT NOW();

如果应用服务器、数据库服务器和用户所在地区使用不同的时区，就可能出现“数据库时间和页面时间相差几个小时”的问题。

因此在分布式系统中，建议明确统一时间存储和展示策略。例如后台统一使用UTC保存时间，在展示层根据用户时区进行转换，避免不同系统之间因为默认时区不同产生偏差。

十二、DATETIME和TIMESTAMP应该如何选择

DATETIME和TIMESTAMP都可以保存日期和时间，但适用场景有所不同。

DATETIME更适合表达一个明确的日期时间，例如：

2026-10-08 18:30:00

它通常不会像TIMESTAMP那样根据时区进行自动转换。

TIMESTAMP与时区处理关系更加密切，适合部分需要表示时间点的业务数据。

选择字段类型时，不要单纯因为“TIMESTAMP名字里有时间戳”就认为它等同于Unix时间戳。Unix时间戳是整数形式的时间表示，而MySQL TIMESTAMP属于数据库日期时间类型。

十三、时间格式转换中的常见错误

1. 把毫秒时间戳直接传给FROM_UNIXTIME()

错误示例：

SQL
SELECT FROM_UNIXTIME(1791455400000);

如果数据是毫秒级时间戳，应先转换为秒：

SQL
SELECT FROM_UNIXTIME(1791455400000 / 1000);

2. DATE_FORMAT中的分钟格式写错

正确写法：

SQL
DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s')

这里：

• %H表示小时

• %i表示分钟

• %s表示秒

3. 直接对非标准字符串进行日期计算

例如：

2026/10/08 18:30

这种数据最好先通过STR_TO_DATE()转换成标准日期时间，再进行比较或计算。

4. 查询条件中对字段使用函数

例如：

SQL
WHERE DATE(create_time) = '2026-10-08'

虽然结果正确，但在大数据量场景下可能影响索引利用。

可以改成：

SQL
WHERE create_time >= '2026-10-08 00:00:00'
  AND create_time < '2026-10-09 00:00:00'

这种方式更适合高性能查询。

十四、时间格式转换的实用建议

数据库设计阶段应尽量避免将日期时间以普通字符串长期保存。如果字段具有明确的时间语义，优先考虑使用DATETIME或TIMESTAMP。

如果系统需要与多个外部平台交互，应提前明确时间戳的单位。尤其需要区分秒级和毫秒级时间戳，否则很容易出现1970年前后的异常日期或者远超正常范围的时间。

对于展示需求，可以在查询层使用DATE_FORMAT()，但如果数据还需要参与排序、范围查询或时间计算，不建议过早转换成字符串。

例如，排序时：

SQL
ORDER BY create_time DESC

通常比对格式化后的字符串排序更加合理。

对于时间范围查询，也应尽量直接操作原始日期字段：

SQL
WHERE create_time >= '2026-10-01'
  AND create_time < '2026-11-01'

而不是先调用日期格式化函数再进行比较。

十五、总结

MySQL时间戳与日期格式转换主要围绕几个核心函数展开：UNIX_TIMESTAMP()负责将日期时间转换为Unix时间戳，FROM_UNIXTIME()负责将Unix时间戳转换为日期时间，DATE_FORMAT()用于日期时间格式化，STR_TO_DATE()则适合将指定格式的字符串解析成MySQL日期时间。

实际使用时还需要特别关注秒级与毫秒级时间戳、数据库时区、索引使用以及DATETIME和TIMESTAMP的类型差异。对于简单的数据展示，可以灵活使用格式化函数；对于高频查询和大数据表，则应优先保证时间字段保持原始类型，并通过合理的范围条件进行检索。