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

wufei123 发布于 2025-09-02 阅读（6）

<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时间戳（秒级）。如果你想把数据库中已有的日期时间字段转换成时间戳，这个函数就派上用场了。比如：
    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));
    。

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

BIGINT
存储13位时间戳是个不错的选择，但务必记住查询时要转换查询条件，而不是列。如果业务更偏向于日期时间维度的分析和报表，或者需要经常使用MySQL的日期函数，那么将数据存储为
DATETIME
类型，并在写入时进行转换，可能会更方便。这两种选择没有绝对的优劣，关键在于匹配你的具体业务场景和查询模式。

以上就是MySQL时间格式转换解析 13位时间戳转日期的高效方法的详细内容，更多请关注知识资源分享宝库其它相关文章！

标签:  高效 时间 格式转换 

相关阅读

发表评论:

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

最近发表

标签列表