MySQL时间戳转换方法 13位数字转日期格式的SQL实现

要将13位毫秒级时间戳转换为可读日期，需先除以1000转为秒级Unix时间戳，再用FROM_UNIXTIME()函数转换为日期时间格式，若时间戳存储为字符串应先用CAST转为数字，为提升查询性能建议添加计算列并建立索引。

在MySQL中，将13位数字（通常代表毫秒级时间戳）转换为可读的日期格式，核心思路是将13位数字除以1000，使其变为标准的10位Unix时间戳（秒级），然后利用

FROM_UNIXTIME()

函数进行转换。

解决方案

要将存储为13位数字的毫秒级时间戳转换为MySQL的日期时间格式，你可以使用如下SQL语句：

SELECT
    FROM_UNIXTIME(your_13_digit_timestamp_column / 1000) AS formatted_datetime
FROM
    your_table_name;

这里，

your_13_digit_timestamp_column

是你存储13位时间戳的列名，

your_table_name

是你的表名。

FROM_UNIXTIME()

函数接受一个Unix时间戳（即从1970年1月1日00:00:00 UTC开始的秒数）作为参数，并将其转换为一个

DATETIME

或

TIMESTAMP

格式的值。由于我们的原始数据是毫秒级的，所以需要先除以1000，将其精确到秒，这样

FROM_UNIXTIME()

才能正确解析。

为什么我的13位数字时间戳转换后结果不对？

这确实是个很常见的“坑”，我个人就遇到过好几次。很多开发者，包括我自己，在初次处理这类数据时，会直觉地把13位数字直接扔给

FROM_UNIXTIME()

。结果呢？不是得到一个看似“古老”的日期，就是直接返回

NULL

或者一个完全不符合预期的值。

根本原因在于

FROM_UNIXTIME()

函数的设计。它期望接收的是一个标准的Unix时间戳，这个时间戳是以秒为单位的，通常是10位数字（例如

1678886400

代表2023年3月15日）。而我们手上的13位数字，比如

1678886400000

，它比10位数字多了三位，这多出来的三位正是毫秒。当你直接把

1678886400000

传给

FROM_UNIXTIME()

时，MySQL会把它当作一个巨大的秒数来处理，这个秒数可能已经远远超出了MySQL能够表示的日期范围，或者指向一个非常遥远的未来（或过去），导致转换失败或结果异常。

所以，解决之道非常简单粗暴，但行之有效：在调用

FROM_UNIXTIME()

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

1678886400000

就会变成

1678886400

，这正是

FROM_UNIXTIME()

所需要的格式。

-- 错误示例：直接转换13位数字，结果可能不正确或为NULL
SELECT FROM_UNIXTIME(1678886400000);

-- 正确示例：先除以1000，再转换
SELECT FROM_UNIXTIME(1678886400000 / 1000);

如何将转换后的日期格式化成我需要的样式？

FROM_UNIXTIME()

默认输出的日期格式通常是

YYYY-MM-DD HH:MM:SS

。但很多时候，我们可能需要更精细或者更简洁的格式，比如只显示日期，或者只显示小时分钟，甚至带上星期几。这时，

DATE_FORMAT()

函数就派上用场了。它允许你根据自己的需求，灵活地定制日期时间的显示格式。

DATE_FORMAT()

的用法是：

DATE_FORMAT(date, format)

。其中

date

就是你通过

FROM_UNIXTIME()

转换出来的日期时间值，而

format

是一个包含各种格式化占位符的字符串。

结合我们之前13位时间戳的转换，完整的格式化SQL语句会是这样：

SELECT
    DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp_column / 1000), '%Y-%m-%d %H:%i:%s') AS full_datetime,
    DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp_column / 1000), '%Y年%m月%d日') AS date_only_chinese,
    DATE_FORMAT(FROM_UNIXTIME(your_13_digit_timestamp_column / 1000), '%H:%i') AS time_only
FROM
    your_table_name;

这里列举一些常用的格式化占位符，你可以根据需要组合它们：

• %Y

  : 四位年份 (e.g., 2023)

• %m

  : 两位月份 (01-12)

• %d

  : 两位日期 (01-31)

• %H

  : 两位小时 (00-23)

• %i

  : 两位分钟 (00-59)

• %s

  : 两位秒 (00-59)

• %W

  : 星期几的完整名称 (e.g., Monday)

• %m

  : 月份的完整名称 (e.g., March)

• %p

  : AM或PM

通过

DATE_FORMAT()

，你可以轻松地将日期时间显示成任何你想要的格式，这在报表生成或者数据展示时非常有用。

处理时间戳时可能遇到的数据类型问题及性能考量

在实际操作中，除了转换逻辑本身，我们还需要考虑一些潜在的数据类型问题和性能影响，特别是当数据量很大的时候。

首先是数据类型。如果你的13位时间戳列是

BIGINT

类型，那恭喜你，直接除以1000通常不会有任何问题。但如果它被存储成了

VARCHAR

或者

TEXT

类型，那在进行数学运算（除法）之前，MySQL需要先将其隐式转换为数字类型。虽然MySQL通常能处理这种隐式转换，但在某些极端情况下（比如字符串中包含非数字字符），这可能会导致转换错误或者

NULL

值。一个更健壮的做法是，如果确定是字符串，先用

CAST(your_column AS UNSIGNED BIGINT)

进行显式转换，然后再除以1000。

-- 如果你的时间戳是字符串类型，建议先CAST
SELECT
    FROM_UNIXTIME(CAST(your_string_timestamp_column AS UNSIGNED BIGINT) / 1000) AS formatted_datetime
FROM
    your_table_name;

其次是性能考量。在一个非常大的表上，如果在

WHERE

子句中对时间戳列应用了

FROM_UNIXTIME()

或除法操作，比如

WHERE FROM_UNIXTIME(timestamp_col / 1000) > '2023-01-01'

，那么MySQL将无法使用该列上的任何索引。这意味着数据库必须对表进行全扫描（

Full Table Scan

），这会显著降低查询性能。

我个人的建议是，如果你的应用需要频繁地根据日期范围查询这些时间戳数据，最好的办法是在数据库层面就将时间戳转换为

DATETIME

或

TIMESTAMP

类型，并存储在一个独立的列中。你可以通过以下几种方式实现：

1. 数据导入/ETL阶段转换： 在数据进入MySQL之前就完成转换，或者在导入后执行一次性的大批量更新。

2. 添加计算列（MySQL 5.7+）： 创建一个

   DATETIME

   类型的生成列（Generated Column），它会自动根据原始的13位时间戳计算并存储日期值。这样，你就可以在这个计算列上创建索引。

   ALTER TABLE your_table_name
   ADD COLUMN converted_datetime DATETIME AS (FROM_UNIXTIME(your_13_digit_timestamp_column / 1000));
   
   -- 然后你就可以在 converted_datetime 列上创建索引了
   CREATE INDEX idx_converted_datetime ON your_table_name (converted_datetime);

3. 应用层处理： 如果查询频率不高，或者数据量不大，那么在应用层进行转换也是一个可行的方案，将转换逻辑放在代码中处理，减轻数据库的负担。

总的来说，理解13位时间戳的本质，并正确地进行单位转换是关键。在此基础上，根据实际的业务场景和数据量，选择合适的数据类型处理和性能优化策略，才能确保你的系统既准确又高效。

以上就是MySQL时间戳转换方法 13位数字转日期格式的SQL实现的详细内容，更多请关注php中文网其它相关文章！