MySQL日期格式化教程 13位时间戳转日期类型的解决方案-mysql教程-PHP中文网

MySQL日期格式化教程 13位时间戳转日期类型的解决方案

星夢妙者

发布： 2025-08-20 11:43:01

原创

265人浏览过

答案是将13位毫秒时间戳除以1000转换为秒，再用FROM_UNIXTIME()函数处理。常见于JavaScript或Java生成的时间戳，MySQL默认函数处理的是10位秒级时间戳，因此需先转换精度。使用DATE_FORMAT()可自定义输出格式；若需保留毫秒，可结合MOD和LPAD拼接。反向转换时，用UNIX_TIMESTAMP()获取秒再乘1000，支持微秒的版本可提取毫秒部分相加。注意时区、数据类型差异及版本兼容性问题。

mysql日期格式化教程 13位时间戳转日期类型的解决方案

当你在MySQL里遇到一个长达13位的数字，却被告知它代表日期时间时，第一反应可能有点懵。这通常意味着你手上的时间戳是毫秒级的，而不是MySQL内置函数期望的秒级Unix时间戳。要把它变成我们熟悉的日期格式，核心操作其实很简单：先除以1000，把毫秒变回秒，然后交给MySQL的日期函数处理。

解决方案： 要将一个存储为13位（毫秒）时间戳的字段转换为可读的日期时间格式，你需要先将其除以1000，然后使用

FROM_UNIXTIME()

登录后复制

函数。如果你还需要特定的格式，可以再套用

DATE_FORMAT()

登录后复制

。

假设你的表名为

my_data

登录后复制

，时间戳字段名为

timestamp_ms

登录后复制

(数据类型通常是

BIGINT

登录后复制

)：

-- 最简单的转换，得到标准的日期时间格式 (YYYY-MM-DD HH:MM:SS)
SELECT FROM_UNIXTIME(timestamp_ms / 1000) AS converted_datetime
FROM my_data;

-- 转换为指定格式，例如只显示日期
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d') AS converted_date
FROM my_data;

-- 转换为指定格式，精确到秒 (MySQL日期类型本身不直接存毫秒，但如果你需要展示，可以考虑字符串拼接或前端处理)
SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS formatted_datetime
FROM my_data;

-- 如果你需要保留毫秒信息，并且你的MySQL版本支持微秒（MySQL 5.6.4+ 支持6位小数精度），你可以这样处理：
SELECT
    CONCAT(
        DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s'),
        '.',
        LPAD(MOD(timestamp_ms, 1000), 3, '0')
    ) AS datetime_with_ms_string
FROM my_data;

登录后复制

这里的关键就是那个

/ 1000

登录后复制

。很多系统，尤其是前端JavaScript或者Java后端，在生成时间戳时习惯用毫秒，而MySQL的

FROM_UNIXTIME

登录后复制

函数默认是处理秒级时间戳的。搞清楚这一点，转换起来就没那么费劲了。

为什么我的MySQL时间戳是13位，而不是常见的10位？

这确实是很多初学者甚至有经验的开发者都会遇到的一个“坑”。我们常说的Unix时间戳，指的是从1970年1月1日00:00:00 UTC到现在的秒数，所以它通常是10位数字（比如

1678886400

登录后复制

）。但如果你发现你的时间戳是13位（比如

1678886400123

登录后复制

），那么恭喜你，你正在处理的是一个毫秒级的时间戳。

这种差异主要来源于不同编程语言和系统对时间戳的约定。

10位时间戳（秒级）: 这是Unix/Linux系统以及C/C++、PHP等语言中
```
time()
```
登录后复制
函数默认的Unix时间戳，也是MySQL
```
FROM_UNIXTIME()
```
登录后复制
函数所期待的格式。它精确到秒。
13位时间戳（毫秒级）: 这种格式在Java的
```
System.currentTimeMillis()
```
登录后复制
、JavaScript的
```
Date.now()
```
登录后复制
以及很多现代API中非常常见。它们提供了更高的精度，精确到毫秒。

所以，当你的数据源是这些系统时，时间戳自然就是13位的。MySQL本身并没有一个直接的函数能把毫秒级时间戳直接转成日期，所以我们才需要手动进行那个除以1000的操作。这并不是MySQL的“缺陷”，只是不同系统间约定俗成的差异。理解了这一点，你就能更好地处理跨系统的数据同步和转换问题了。我个人觉得，知道这个背景能省去不少调试的麻烦。

如何将MySQL日期类型反向转换为13位时间戳？

既然我们知道如何把13位时间戳转成日期，那反过来，把MySQL里的日期时间类型（比如

DATETIME

登录后复制

或

TIMESTAMP

登录后复制

字段）转换成13位毫秒时间戳，这需求也挺常见的。比如，你要把数据传给一个需要毫秒时间戳的API接口，或者写入一个存储毫秒时间戳的外部系统。

比格设计

比格设计是135编辑器旗下一款一站式、多场景、智能化的在线图片编辑器

124

查看详情

这个过程其实就是逆向操作：先用

UNIX_TIMESTAMP()

登录后复制

函数把日期时间转成10位秒级时间戳，然后再乘以1000。

-- 假设你的表名为 my_events，有一个字段 event_time 类型是 DATETIME 或 TIMESTAMP
SELECT
    event_time,
    UNIX_TIMESTAMP(event_time) AS timestamp_seconds,
    UNIX_TIMESTAMP(event_time) * 1000 AS timestamp_milliseconds_approx
FROM my_events;

-- 如果你的MySQL版本支持微秒（MySQL 5.6.4+），并且你的DATETIME/TIMESTAMP字段存储了微秒精度（例如 DATETIME(3) 或 DATETIME(6)），
-- 那么你可以更精确地转换为13位毫秒时间戳：
SELECT
    event_time,
    -- 获取秒数部分，并乘以1000
    FLOOR(UNIX_TIMESTAMP(event_time)) * 1000
    -- 获取微秒部分，除以1000得到毫秒，并取整
    + FLOOR(MICROSECOND(event_time) / 1000) AS precise_timestamp_ms
FROM my_events;

-- 如果要获取当前时间的13位毫秒时间戳，并且你的MySQL版本支持 NOW(N) 和 UNIX_TIMESTAMP(N)：
-- （注意：UNIX_TIMESTAMP(NOW(N)) 在MySQL 8.0.17+ 才能保留微秒精度）
SELECT UNIX_TIMESTAMP(NOW(3)) * 1000 AS current_timestamp_ms_if_supported;

-- 对于较老版本或确保兼容性，可以手动拼接：
SELECT
    FLOOR(UNIX_TIMESTAMP(NOW())) * 1000 + FLOOR(MICROSECOND(NOW()) / 1000) AS current_timestamp_ms_manual;

登录后复制

需要注意的是，MySQL的

DATETIME

登录后复制

和

TIMESTAMP

登录后复制

类型在MySQL 5.6.4版本之前是不存储毫秒或微秒的。如果你在老版本上使用，

UNIX_TIMESTAMP()

登录后复制

只会给你秒级的精度。如果你真的需要毫秒精度，并且你的MySQL版本支持（5.6.4+支持微秒，即

DATETIME(6)

登录后复制

），那么在转换回13位时间戳时，就需要额外处理微秒部分。这事儿听起来有点绕，但实际操作起来就是确保数据源和目标格式的精度匹配。

在MySQL中处理日期和时间，还有哪些值得注意的细节？

处理日期和时间，远不止时间戳转换这么简单。我在日常工作中，总会遇到一些小细节，处理不好就可能导致数据错误或者性能问题。

时区问题：这是个老大难。MySQL服务器有自己的时区设置，你的数据库连接也有时区设置，应用程序也有时区设置。
```
NOW()
```
登录后复制
函数返回的是服务器当前时区的时间，
```
UTC_TIMESTAMP()
```
登录后复制
返回的是UTC时间。如果你不明确处理时区，跨地域的业务数据就可能出现时间偏差。比如，一个用户在北京提交的订单，在纽约的后台看，时间可能就对不上了。我通常会建议所有数据存储都使用UTC时间，然后在应用程序层面根据用户所在时区进行展示转换，这样能最大程度避免混乱。
```
CONVERT_TZ()
```
登录后复制
函数在MySQL内部转换时区时很有用，但用不好也容易出错。
数据类型选择：
- ```
DATETIME
```
  登录后复制
  ：存储日期和时间，范围广（'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'），不带时区信息。如果你需要存储未来很远或者过去很久的时间，它是个不错的选择。
- ```
TIMESTAMP
```
  登录后复制
  ：也存储日期和时间，但范围相对小（'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC），而且它会根据MySQL服务器的时区设置自动转换。这意味着，如果你把一个
```
TIMESTAMP
```
  登录后复制
  值存进去，然后改变了服务器时区，再取出来时，时间可能会“变”了。这特性有时候很方便，

以上就是MySQL日期格式化教程 13位时间戳转日期类型的解决方案的详细内容，更多请关注php中文网其它相关文章！