MySQL日期转换方案 13位时间戳转标准格式的完整教程-小浪学习网

要将mysql中的13位毫秒级时间戳转换为标准日期格式，必须先将其除以1000转换为10位秒级时间戳，再使用from_unixtime()函数进行转换，例如select from_unixtime(your_13_digit_timestamp / 1000) as standard_datetime from your_table_name；若需自定义输出格式，可结合date_format()函数，如按年月日时分秒显示可使用date_format(from_unixtime(your_13_digit_timestamp / 1000), ‘%y-%m-%d %h:%i:%s’)；实际应用中需注意时区问题，建议统一存储utc时间并在应用层根据用户时区展示，同时确保时间戳字段使用bigint类型以避免溢出，对于大规模数据应避免在查询中对时间戳列使用函数导致索引失效，推荐将日期范围转换为时间戳后直接比较以提升性能，此外处理NULL值时可结合coalesce函数设定默认值，遵循这些方法可准确高效地完成时间戳转换并规避常见陷阱。

MySQL日期转换方案 13位时间戳转标准格式的完整教程

mysql中将13位时间戳（毫秒级）转换为标准日期格式，核心在于将其转换为10位时间戳（秒级），再利用

FROM_UNIXTIME()

函数。简单来说，就是把你的13位时间戳除以1000，然后交给MySQL处理，它就能帮你准确地还原出日期和时间。

解决方案

我发现很多朋友在处理MySQL里的时间戳时，特别容易在13位和10位之间犯迷糊。我们日常看到的或者系统生成的很多时间戳，尤其是前端或者某些API返回的，往往是13位的，精确到毫秒。但MySQL内置的

FROM_UNIXTIME()

函数，它默认识别的是10位的Unix时间戳，也就是精确到秒的。所以，这中间就差了三个零。

要解决这个问题，其实非常直接，就是做个简单的除法。你需要将13位的毫秒级时间戳除以1000，把它降维成秒级，然后再使用

FROM_UNIXTIME()

。

假设你的13位时间戳字段叫做

your_13_digit_timestamp

，你可以这样进行转换：

SELECT     FROM_UNIXTIME(your_13_digit_timestamp / 1000) AS standard_datetime FROM     your_table_name;

这条sql语句会把

your_13_digit_timestamp

列中的每一个值都除以1000，然后通过

FROM_UNIXTIME()

函数将其转换成

'yyYY-MM-DD HH:MM:SS'

这样的标准日期时间格式。

如果你需要更精细的格式控制，比如只显示日期、或者只显示小时分钟，那就可以结合

DATE_FORMAT()

函数来用。这就像是给你的日期时间穿上不同的“衣服”。

SELECT     DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y-%m-%d %H:%i:%s') AS formatted_datetime_full,     DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y-%m-%d') AS formatted_date_only,     DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%H:%i:%s') AS formatted_time_only FROM     your_table_name;

在实际操作中，确保你的13位时间戳字段的数据类型是

BIGINT

，这样才能完整存储那么长的数字。如果存成了

INT

，那肯定会溢出，数据就不对了。

为什么我的13位时间戳转换后总是不对？

这几乎是我在职业生涯中被问到频率最高的问题之一了。说实话，我刚开始接触这块的时候，也踩过不少坑。核心原因，就像前面提到的，就是单位不对等。你给MySQL的是毫秒，但它期待的是秒。

Unix时间戳（Unix timestamp）是一个很重要的概念，它通常指的是从1970年1月1日00:00:00 UTC（协调世界时）开始经过的秒数。所以，当你在MySQL里使用

FROM_UNIXTIME()

时，它就是按照这个“秒数”的定义去解析的。

如果你手头有一个13位的时间戳，比如

1678886400000

，它代表的是2023年3月15日00:00:00 GMT。如果你直接把它丢给

FROM_UNIXTIME()

，不除以1000，MySQL会把它当作一个非常非常遥远的未来时间，因为它会认为这是

1678886400000

秒。这简直是天文数字，转换出来的结果可能会是

55219-09-17 08:00:00

（具体取决于你的时区），这显然不是你想要的结果。

所以，当你发现转换出来的日期时间“不对劲”，或者“太未来了”，那八成就是忘了除以1000。这就像是你手上有一堆毫秒级的数据，但数据库只认秒，你不做这个单位换算，那结果肯定天差地别。别看这只是一个简单的除法，但它背后的逻辑是理解Unix时间戳的关键。

除了标准格式，我还能将时间戳转换为哪些日期格式？

MySQL在日期时间格式化方面提供了非常强大的功能，主要就是通过

DATE_FORMAT()

函数来实现。它允许你根据各种格式代码（format specifiers）来定义输出的日期时间字符串。这就像是给数据穿上各种定制的衣服，满足不同展示需求。

这里列举一些常用的格式代码，你可以随意组合：

```
%Y
```
: 四位年份 (e.g., 2023)
```
%Y
```
: 两位年份 (e.g., 23)
```
%m
```
: 两位月份 (01-12)
```
%c
```
: 月份 (1-12)
```
%d
```
: 两位日期 (01-31)
```
%e
```
: 日期 (1-31)
```
%H
```
: 两位小时 (00-23, 24小时制)
```
%H
```
: 两位小时 (01-12, 12小时制)
```
%I
```
: 两位小时 (01-12, 12小时制)
```
%I
```
: 两位分钟 (00-59)
```
%s
```
: 两位秒数 (00-59)
```
%f
```
: 微秒 (000000-999999) – 注意13位时间戳是毫秒，这个是微秒
```
%p
```
: AM或PM
```
%W
```
: 星期几的完整名称 (e.g., Monday)
```
%a
```
: 星期几的缩写 (e.g., Mon)
```
%j
```
: 一年中的第几天 (001-366)

结合

FROM_UNIXTIME(your_13_digit_timestamp / 1000)

，你可以玩出很多花样：

只获取日期部分 (YYYY-MM-DD):

SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y-%m-%d') AS date_only;

只获取时间部分 (HH:MM:SS):

SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%H:%i:%s') AS time_only;

获取带AM/PM的12小时制时间:

SELECT DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%h:%i:%s %p') AS time_12_hour;

获取中文星期几: 这需要一点技巧，通常是结合

ELT()

和

DAYOFWEEK()

函数，因为

DATE_FORMAT

本身不直接支持中文。

SELECT     CONCAT(DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp / 1000), '%Y年%m月%d日 '),            ELT(DAYOFWEEK(FROM_UNIXTIME(your_13_digit_timestamp / 1000)), '星期日', '星期一', '星期二', '星期三', '星期四', '星期五', '星期六')) AS formatted_chinese_date;

这个例子稍微复杂一点，但它展示了

DATE_FORMAT

的灵活性以及与其他函数结合的可能性。掌握了这些格式代码，你就拥有了对日期时间输出格式的完全控制权。

在实际应用中，处理时间戳转换有哪些常见陷阱和最佳实践？

实际开发中，时间戳转换远不止一个简单的除法那么轻松。我遇到过不少因为处理不当导致的数据混乱、时区错位甚至性能问题。这里我总结一些常见的陷阱和一些我个人认为的最佳实践。

常见陷阱：

时区问题： 这是最最常见的陷阱，没有之一。
FROM_UNIXTIME()
函数默认会使用MySQL服务器或当前会话的时区来解释时间戳。如果你的时间戳是UTC时间，但服务器设置的是CST（北京时间），那转换出来的结果就会有8小时的偏差。我见过很多系统，前端传的是UTC时间戳，后端直接存，展示的时候又没做时区转换，结果用户看到的时间总是“不对”。
- 例子： UTC
```
1678886400
```
  （2023-03-15 00:00:00 UTC）在东八区服务器上直接
  FROM_UNIXTIME()
  ，会显示为
```
2023-03-15 08:00:00
```
  。
数据类型选择不当： 有些开发者习惯性地把时间戳字段定义为
```
VARCHAR
```
。这会带来两个问题：一是存储效率低，二是进行日期计算或排序时会非常麻烦，需要额外的类型转换，性能受损。
NULL值处理： 如果你的时间戳字段允许为
```
NULL
```
，那么在执行转换时，
```
FROM_UNIXTIME(NULL)
```
会返回
```
NULL
```
，这通常是符合预期的。但如果你有特定的需求，比如希望
```
NULL
```
时间戳显示为某个默认值，就需要用
```
COALESCE()
```
或其他条件判断。
大规模数据转换性能： 对于包含数百万甚至上亿行记录的表，在查询时对时间戳列进行实时的
```
FROM_UNIXTIME(timestamp / 1000)
```
转换，会带来显著的性能开销，因为它无法利用索引。

最佳实践：

明确时区策略：
- 统一存储UTC时间： 我强烈建议数据库中所有时间戳都统一存储为UTC时间（无论10位还是13位）。这是国际化应用的标准做法，可以避免大量时区转换的麻烦。
- 在应用层处理时区转换： 在向用户展示时，根据用户的时区偏好进行转换。MySQL也提供了
```
CONVERT_TZ()
```
  函数，但通常在应用层处理更灵活。
- 设置会话时区： 如果你的应用确实需要在MySQL层面处理时区，可以在连接数据库后，通过
```
SET time_zone = 'your_timezone';
```
  来设置当前会话的时区。
选择正确的数据类型：
- 存储13位时间戳： 使用
  BIGINT
  类型。
- 存储10位时间戳： 使用
```
INT
```
  或
  BIGINT
  。
- 直接存储日期时间： 如果你不需要时间戳的原始数字形式，并且主要进行日期时间查询和计算，那么直接使用MySQL的
```
DATETIME
```
  或
```
TIMESTAMP
```
  类型是更优的选择。它们有内置的日期时间函数，索引效率高，也更直观。例如，在数据写入前，就将13位时间戳在应用层转换成
```
DATETIME
```
  格式再插入。
索引优化： 如果你的查询经常涉及时间范围筛选（例如
```
WHERE your_13_digit_timestamp BETWEEN ... AND ...
```
），确保
your_13_digit_timestamp
字段上有索引。但要注意，如果查询条件里包含了
FROM_UNIXTIME()
这样的函数，索引可能就失效了。
- 解决方案： 尽量避免在
```
WHERE
```
  子句中对索引列使用函数。例如，如果查询某个日期范围，可以把日期范围转换为时间戳再进行比较：
```
-- 查找2023年3月15日当天的数据 SELECT * FROM your_table_name WHERE your_13_digit_timestamp >= UNIX_TIMESTAMP('2023-03-15 00:00:00') * 1000   AND your_13_digit_timestamp < UNIX_TIMESTAMP('2023-03-16 00:00:00') * 1000;
```
  这样
  your_13_digit_timestamp
  上的索引就能被有效利用。
数据清洗和迁移： 如果你现有数据是13位时间戳且存储为
```
VARCHAR
```
或不规范，考虑进行一次性数据清洗和类型迁移。这可能涉及编写脚本批量转换和更新数据，确保数据质量。