MySQL时间格式转换解析 13位时间戳转日期的高效方法

絕刀狂花

发布时间：2025-09-01 11:10:01

444人浏览过

来源于php中文网

原创

<p>要将mysql中的13位时间戳转换为日期，必须先将其除以1000转换为10位秒级时间戳，再使用from_unixtime函数进行转换，因为mysql的from_unixtime函数仅接受秒级时间戳；直接使用13位时间戳会导致转换错误或超出范围，正确做法是执行select from_unixtime(timestamp_ms / 1000) as converted_datetime from your_table；若需格式化输出，可使用select from_unixtime(timestamp_ms / 1000, '%y-%m-%d %h:%i:%s') as formatted_datetime from your_table；在查询性能优化方面，应避免在where子句中对时间戳字段使用函数，而应将日期范围转换为13位时间戳后直接比较，以利用索引提升查询效率，例如通过set @start_ts_ms = unix_timestamp('2023-03-15 00:00:00') * 1000计算起始时间戳，并在查询中使用where timestamp_ms >= @start_ts_ms and timestamp_ms <= @end_ts_ms来确保索引有效使用；此外，根据业务需求选择bigint存储毫秒级时间戳或datetime存储标准日期时间类型，可平衡存储效率与查询便利性，最终方案应结合数据来源、查询模式和性能要求综合决策。</p>

MySQL时间格式转换解析 13位时间戳转日期的高效方法

要将MySQL中的13位时间戳转换为日期，核心在于理解MySQL的

FROM_UNIXTIME

函数默认处理的是秒级时间戳。因此，你需要将13位（毫秒级）时间戳除以1000，使其变为10位（秒级）时间戳，然后再进行转换。

解决方案

我们经常会遇到从前端或者其他系统（比如Java的

System.currentTimeMillis()

）拿到一个13位的数字，它代表的是从Unix纪元（1970年1月1日00:00:00 UTC）开始到现在的毫秒数。而MySQL内置的

FROM_UNIXTIME()

函数，它期待的参数是秒级的Unix时间戳。这中间就差了三个零，也就是1000倍。

所以，最直接、最有效的方法就是在使用

FROM_UNIXTIME()

函数之前，先将你的13位时间戳除以1000。

假设你的表名是

your_table

，存储13位时间戳的字段是

timestamp_ms

（通常会是

BIGINT

类型），那么转换的SQL语句会是这样：

SELECT
    FROM_UNIXTIME(timestamp_ms / 1000) AS converted_datetime
FROM
    your_table;

如果你需要特定的日期时间格式，比如只显示年月日时分秒，可以给

FROM_UNIXTIME

函数传入第二个参数，指定格式字符串：

SELECT
    FROM_UNIXTIME(timestamp_ms / 1000, '%Y-%m-%d %H:%i:%s') AS formatted_datetime
FROM
    your_table;

这个方法简单粗暴，但确实是最符合MySQL原生函数设计逻辑的。我个人在处理这类跨系统时间戳问题时，几乎都是第一时间想到这个办法，因为它既直观又高效。

为什么我的13位时间戳在MySQL中无法直接转换？

这其实是个很常见的“坑”，尤其是当你从一个Java或者JavaScript背景转到MySQL，或者反之。核心原因在于，不同系统对“时间戳”这个概念的默认精度定义不同。

我们常说的Unix时间戳，它的标准定义是从1970年1月1日00:00:00 UTC到现在的秒数。所以，它是一个10位的整数（比如

1678886400

）。这正是MySQL的

FROM_UNIXTIME()

函数所期望的。

然而，在很多现代编程语言和系统中，为了更高的精度，时间戳被扩展到了毫秒级。比如Java的

System.currentTimeMillis()

或者JavaScript的

Date.now()

，它们返回的都是13位数字（比如

1678886400000

），这比秒级时间戳多了三位，代表的是毫秒。

所以，当你尝试直接把一个13位的毫秒级时间戳扔给

FROM_UNIXTIME()

时，MySQL会把它当作一个非常巨大的秒级时间戳来处理。结果可想而知，它会转换出一个遥远的未来日期，或者直接返回NULL，因为这个“秒数”可能超出了它能表示的范围，或者超出了日期时间类型的有效范围。

理解这个精度差异是解决问题的关键。它不是MySQL的“bug”，而是不同系统之间对“时间戳”这一概念的约定不同。

除了FROM_UNIXTIME，还有哪些方法可以处理MySQL中的时间戳？

虽然

FROM_UNIXTIME

是处理Unix时间戳（无论是秒级还是通过除法转换为秒级）的首选，但MySQL在时间处理上还有其他强大的函数，它们各有侧重，但可能不直接用于“13位时间戳转日期”这个特定场景。

```
UNIX_TIMESTAMP()
```
: 这个函数是
```
FROM_UNIXTIME()
```
的反向操作。它将一个
```
DATETIME
```
或
```
TIMESTAMP
```
类型的值转换为10位的Unix时间戳（秒级）。如果你想把数据库中已有的日期时间字段转换成时间戳，这个函数就派上用场了。比如：

ModelGate
一站式AI模型管理与调用工具

下载
```
SELECT UNIX_TIMESTAMP(NOW()) AS current_unix_timestamp;
```
如果你需要13位毫秒级时间戳，MySQL本身没有直接提供，通常需要应用程序层进行拼接（
```
UNIX_TIMESTAMP() * 1000
```
）。
```
DATE_FORMAT()
```
: 这个函数用于将
```
DATETIME
```
、
```
DATE
```
或
```
TIMESTAMP
```
类型的值格式化为指定格式的字符串。它不涉及时间戳转换，而是针对已经存在的日期时间类型进行显示上的调整。
```
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒') AS formatted_chinese_date;
```
它不能直接将数字时间戳转换为日期，但如果你已经通过
```
FROM_UNIXTIME
```
得到了日期类型，再用它来精细化输出格式就非常方便。
```
STR_TO_DATE()
```
: 这个函数是
```
DATE_FORMAT()
```
的反向操作。它将一个字符串按照指定的格式解析并转换为
```
DATE
```
或
```
DATETIME
```
类型。当你从外部接收到的是一个格式化的日期时间字符串（比如
```
'2023-03-15 10:30:00'
```
），并且需要将其存入
```
DATETIME
```
字段时，它非常有用。
```
SELECT STR_TO_DATE('2023-03-15 10:30:00', '%Y-%m-%d %H:%i:%s') AS parsed_datetime;
```
显然，这个也和直接转换数字时间戳不是一个路子。

所以，回到13位时间戳转日期这个需求，

FROM_UNIXTIME(timestamp_ms / 1000)

仍然是核心，其他函数更多是在不同场景下辅助日期时间处理的。

在MySQL中处理时间戳时，常见的性能问题与优化策略是什么？

在数据库层面处理时间戳，尤其是涉及到查询和索引时，性能问题是需要特别留意的。我见过不少因为时间戳处理不当导致查询效率直线下降的案例。

一个非常常见的性能陷阱是在

WHERE

子句中对索引列使用函数。假设你的

timestamp_ms

字段上有一个索引，你可能会这样写查询：

-- 性能差的例子
SELECT * FROM your_table
WHERE FROM_UNIXTIME(timestamp_ms / 1000, '%Y-%m-%d') = '2023-03-15';

或者

-- 性能差的例子
SELECT * FROM your_table
WHERE FROM_UNIXTIME(timestamp_ms / 1000) BETWEEN '2023-03-15 00:00:00' AND '2023-03-15 23:59:59';

这样写的问题在于，MySQL在执行查询时，需要对

timestamp_ms

字段的每一行都先执行

FROM_UNIXTIME(timestamp_ms / 1000)

这个函数，然后才能进行比较。这意味着它无法直接利用

timestamp_ms

字段上的索引。索引就像一本书的目录，但你现在要求它先“翻译”每一页的内容，再根据翻译后的内容去查目录，这目录就形同虚设了。这会导致全表扫描，对于大数据量来说是灾难性的。

优化策略是：将函数应用到查询条件的值上，而不是索引列上。 如果你想查询某个日期范围的数据，你应该将日期范围转换为对应的13位时间戳范围，然后直接用时间戳进行比较。

-- 优化后的例子：查询2023年3月15日全天的数据
-- 首先，计算2023-03-15 00:00:00 的13位时间戳
SET @start_ts_ms = UNIX_TIMESTAMP('2023-03-15 00:00:00') * 1000;
-- 然后，计算2023-03-15 23:59:59 的13位时间戳
SET @end_ts_ms = UNIX_TIMESTAMP('2023-03-15 23:59:59') * 1000;

SELECT * FROM your_table
WHERE timestamp_ms >= @start_ts_ms AND timestamp_ms <= @end_ts_ms;

这样，

timestamp_ms

字段可以直接利用其上的索引，查询效率会大大提升。

另一个相关的考量是数据类型选择：

BIGINT
存储时间戳（毫秒级）：这种方式存储占用空间小，数值比较快。但缺点是可读性差，需要函数转换才能理解。在
```
WHERE
```
子句中，如上所述，只要比较值是预先转换好的时间戳，索引就能很好地工作。
DATETIME
或
TIMESTAMP
存储日期时间：这种方式可读性好，可以直接进行日期时间函数操作，且MySQL对这类字段有很好的优化和索引支持。如果业务需求更多是日期时间范围查询和显示，直接存储为
```
DATETIME
```
会更自然。但如果你原始数据就是毫秒级时间戳，插入时需要转换，比如
```
INSERT INTO your_table (datetime_col) VALUES (FROM_UNIXTIME(timestamp_ms / 1000));
```
。

在我看来，如果你主要做的是精确到毫秒的时间记录，且查询大部分是基于时间戳范围的，