MySQL时间戳转换技巧详解13位时间戳转日期格式的实现方法-mysql教程-PHP中文网

MySQL时间戳转换技巧详解13位时间戳转日期格式的实现方法

爱谁谁

发布： 2025-08-23 08:05:01

原创

340人浏览过

13位时间戳是毫秒级Unix时间戳，需除以1000转为秒级再用FROM_UNIXTIME()转换为日期，结合DATE_FORMAT()可自定义格式，注意数据类型、NULL值、时区及索引性能问题。

mysql时间戳转换技巧详解13位时间戳转日期格式的实现方法

在MySQL中处理13位时间戳（通常是毫秒级）并将其转换为可读的日期格式，核心思路其实很简单：你需要先将这个毫秒级时间戳转换为秒级，然后再使用MySQL内置的

FROM_UNIXTIME()

登录后复制

函数。具体操作就是将13位时间戳除以1000。

解决方案

要将一个存储为13位（毫秒）的时间戳字段（假设名为

timestamp_ms

登录后复制

，类型为

BIGINT

登录后复制

）转换为标准的日期时间格式，你可以使用以下SQL语句：

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

登录后复制

如果你需要更精细的日期时间格式控制，可以结合

DATE_FORMAT()

登录后复制

函数：

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

登录后复制

这基本上就是解决问题的关键。我个人在处理来自各种系统（比如前端JavaScript的

Date.now()

登录后复制

或者Java的

System.currentTimeMillis()

登录后复制

）传来的时间戳时，经常会遇到这种13位的情况。一开始可能会有点疑惑，因为MySQL的

FROM_UNIXTIME

登录后复制

默认是针对10位秒级时间戳设计的，但只要理解了毫秒和秒的换算关系，一切就迎刃而解了。

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

这个问题问得好，这其实是很多开发者初次遇到时会有的疑问。简单来说，时间戳的位数差异反映了其精度。

我们常说的“Unix时间戳”或者“Epoch时间”通常是指从1970年1月1日00:00:00 UTC（协调世界时）开始经过的秒数，这是一个10位的整数。这是最传统、最广泛使用的Unix时间戳格式。

然而，在现代应用开发中，尤其是在需要更高时间精度或者跨语言、跨平台数据交换的场景下，毫秒级时间戳变得非常流行。例如：

JavaScript的
Date.now()
登录后复制
：这个函数返回的就是当前时间的毫秒数，一个典型的13位数字（例如：
```
1678886400000
```
登录后复制
）。
Java的
System.currentTimeMillis()
登录后复制
：同样，它也返回当前时间的毫秒数，也是13位。
一些前端框架或API设计： 为了捕获更细粒度的事件发生时间，或者在分布式系统中保持时间同步的精确性，很多系统会选择存储毫秒级时间戳。

所以，当你在MySQL数据库中看到13位时间戳时，几乎可以肯定它代表的是毫秒级的Unix时间戳。这并不是什么“错误”，而是一种更高精度的时间表示方式。我曾经手头的一个项目，后端是Java，前端是React，数据传递过程中就自然而然地使用了毫秒时间戳，存入数据库时也原样保留，直到需要报表展示时才进行转换。

如何将13位时间戳精确转换为特定日期格式？

将13位时间戳转换为特定日期格式，我们主要依赖

FROM_UNIXTIME()

登录后复制

和

DATE_FORMAT()

登录后复制

这两个函数。

FROM_UNIXTIME()

登录后复制

负责将Unix时间戳（秒级）转换为MySQL的

DATETIME

登录后复制

或

TIMESTAMP

登录后复制

类型，而

DATE_FORMAT()

登录后复制

则允许你自定义输出的日期时间字符串格式。

ViiTor实时翻译

AI实时多语言翻译专家！强大的语音识别、AR翻译功能。

116

查看详情

正如前面提到的，关键在于先将13位毫秒时间戳除以1000，得到10位秒级时间戳。然后，你可以使用

DATE_FORMAT()

登录后复制

函数的各种格式化符号来达到你想要的精确格式。

-- 假设你的13位时间戳字段是 event_timestamp_ms
SELECT
    DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS "精确到秒",
    DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%Y年%m月%d日 %H时%i分%s秒') AS "中文格式",
    DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%W, %M %D, %Y %r') AS "英文口语化格式",
    DATE_FORMAT(FROM_UNIXTIME(event_timestamp_ms / 1000), '%Y%m%d%H%i%s') AS "无分隔符格式"
FROM
    your_log_table;

登录后复制

这里列举了一些常用的格式化符号：

```
%Y
```
登录后复制
: 四位年份 (e.g., 2023)
```
%m
```
登录后复制
: 两位月份 (01-12)
```
%d
```
登录后复制
: 两位日期 (01-31)
```
%H
```
登录后复制
: 两位小时 (00-23)
```
%i
```
登录后复制
: 两位分钟 (00-59)
```
%s
```
登录后复制
: 两位秒 (00-59)
```
%f
```
登录后复制
: 微秒 (000000-999999) - 注意：
```
FROM_UNIXTIME
```
登录后复制
本身不直接支持毫秒或微秒精度，它处理的是秒。如果你原始的13位时间戳需要保留毫秒精度，你可能需要单独处理毫秒部分，或者考虑MySQL 5.6.4+版本引入的
```
DATETIME(N)
```
登录后复制
类型，它支持微秒精度，但转换时仍需技巧。对于13位时间戳，通常我们只取到秒级。
```
%W
```
登录后复制
: 星期几的完整名称 (e.g., Monday)
```
%m
```
登录后复制
: 月份的完整名称 (e.g., March)
```
%d
```
登录后复制
: 带有英文后缀的日期 (e.g., 1st, 2nd)
```
%r
```
登录后复制
: 12小时制时间 (e.g., 09:30:00 PM)

选择合适的格式化符号组合，就能满足绝大多数的日期时间显示需求。

转换过程中可能遇到的坑及优化建议？

尽管转换看起来简单，但在实际应用中，还是有一些“坑”需要留意，以及一些优化建议可以考虑。

可能遇到的坑：

数据类型问题： 最常见的就是时间戳字段不是数字类型（比如是
```
VARCHAR
```
登录后复制
）。如果你的13位时间戳被存储为字符串，那么直接进行
```
/ 1000
```
登录后复制
的数学运算可能会出错，或者导致隐式类型转换，影响性能。我建议这类时间戳字段应该始终是
```
BIGINT
```
登录后复制
类型，确保数据完整性和计算效率。如果确实是
```
VARCHAR
```
登录后复制
，你可能需要先用
```
CAST(timestamp_str AS UNSIGNED)
```
登录后复制
或
```
CONVERT(timestamp_str, UNSIGNED)
```
登录后复制
进行转换。
空值（NULL）处理： 如果你的时间戳字段中存在
```
NULL
```
登录后复制
值，
```
FROM_UNIXTIME(NULL)
```
登录后复制
会返回
```
NULL
```
登录后复制
。这通常是符合预期的行为，但如果你希望
```
NULL
```
登录后复制
时间戳显示为特定的默认值（比如空字符串或某个固定日期），你需要使用
```
IFNULL()
```
登录后复制
或
```
COALESCE()
```
登录后复制
函数来处理。
无效的时间戳： 极少数情况下，你可能会遇到非法的数字（例如负数，或者一个远远超出合理范围的巨大数字）。
```
FROM_UNIXTIME()
```
登录后复制
对于负数或非常大的数字可能会返回
```
NULL
```
登录后复制
或不预期的结果。在应用层插入数据前进行校验是个好习惯。
时区问题：
```
FROM_UNIXTIME()
```
登录后复制
默认返回的是服务器时区的时间。如果你的13位时间戳是UTC时间，而你的MySQL服务器时区不是UTC，那么转换后的时间可能会有偏差。为了确保准确性，通常建议在数据库中统一存储UTC时间戳，并在显示时根据用户所在时区进行转换，或者在MySQL连接时设置会话时区。

优化建议：

索引考量： 如果你经常需要根据转换后的日期时间进行查询（例如
```
WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d') = '2023-03-15'
```
登录后复制
），那么这种写法会导致函数内部执行，无法利用
```
timestamp_ms
```
登录后复制
字段上的索引。这对于大数据量查询来说是个性能杀手。
- 解决方案： 考虑在表中新增一个
```
DATETIME
```
  登录后复制
  类型的列，专门用于存储转换后的日期时间，并在这个新列上建立索引。你可以在数据写入时同步更新这个日期时间列，或者使用触发器来自动维护。
- 另一种方式是，如果查询条件是日期范围，可以尝试将日期范围转换为13位时间戳范围进行查询，例如：
```
WHERE timestamp_ms BETWEEN UNIX_TIMESTAMP('2023-03-15 00:00:00') * 1000 AND UNIX_TIMESTAMP('2023-03-15 23:59:59') * 1000
```
  登录后复制
  。这样可以利用
```
timestamp_ms
```
  登录后复制
  上的索引。
应用层转换： 如果你的应用对性能要求极高，并且大部分时间戳的转换都发生在数据读取之后，考虑将13位时间戳直接传给应用层，在应用代码中进行转换和格式化。大多数编程语言处理时间戳和日期格式化的效率都非常高。
视图（View）： 对于经常需要转换并展示的场景，可以创建一个视图，将转换逻辑封装在视图中。这样，每次查询视图时，都会得到已经转换好的日期时间，简化了查询语句。

-- 创建一个视图，包含转换后的日期时间
CREATE VIEW your_table_with_datetime_view AS
SELECT
    *, -- 包含所有原始字段
    DATE_FORMAT(FROM_UNIXTIME(timestamp_ms / 1000), '%Y-%m-%d %H:%i:%s') AS formatted_datetime
FROM
    your_table_name;

-- 查询视图
SELECT * FROM your_table_with_datetime_view WHERE formatted_datetime LIKE '2023-03%';

登录后复制

请注意，虽然视图可以简化查询，但其底层仍然是执行函数转换，因此对大量数据的过滤查询性能影响与直接使用函数相同。所以，索引优化方案通常是更根本的解决之道。

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