MySQL时间戳转日期函数详解 where查询格式化方法解析-mysql教程-PHP中文网

MySQL中转换时间戳与日期需用FROM_UNIXTIME和UNIX_TIMESTAMP函数，WHERE查询时应将日期转为时间戳以利用索引，避免函数作用于字段导致全表扫描，提升性能。

mysql时间戳转日期函数详解 where查询格式化方法解析

MySQL中将时间戳转换为日期，主要依靠

FROM_UNIXTIME

登录后复制

函数，它能将Unix时间戳转化为可读的日期时间格式。在

WHERE

登录后复制

子句中进行时间戳的日期格式化查询时，关键在于确保比较的数据类型一致性，通常是将时间戳字段也转换为日期格式进行比较，或者更推荐的做法是将日期常量转换为时间戳，以利用索引提升查询效率。

解决方案

处理MySQL中的时间戳与日期转换，尤其是当我们需要在

WHERE

登录后复制

子句中基于日期进行筛选时，理解

FROM_UNIXTIME()

登录后复制

函数是核心。这个函数接受一个Unix时间戳（即从'1970-01-01 00:00:00' UTC到现在的秒数），并将其转换成一个可读的日期时间字符串或

DATETIME

登录后复制

类型。

基本用法：

SELECT FROM_UNIXTIME(1678886400);
-- 结果可能是 '2023-03-15 00:00:00'

SELECT FROM_UNIXTIME(1678886400, '%Y-%m-%d %H:%i:%s');
-- 结果 '2023-03-15 00:00:00'

登录后复制

第二个参数是格式字符串，和

DATE_FORMAT()

登录后复制

函数用的格式符一样，这给了我们极大的灵活性。

在

WHERE

登录后复制

子句中的应用与考量：

当你的表里有一个

INT

登录后复制

或

BIGINT

登录后复制

类型的字段存储的是Unix时间戳，而你想根据某个日期范围来查询，直接的想法可能是：

-- 假设你的时间戳字段是 `created_at`
SELECT *
FROM your_table
WHERE FROM_UNIXTIME(created_at, '%Y-%m-%d') = '2023-03-15';

登录后复制

这种写法确实能达到目的，但这里有个隐患，就是它对

created_at

登录后复制

字段应用了函数。如果

created_at

登录后复制

字段上有索引，那么这种写法会使得索引失效，导致全表扫描，这在数据量大时会成为性能瓶颈。我个人在处理这类查询时，总是会优先考虑如何让索引发挥作用。

更推荐的做法是，将你要比较的日期常量转换成时间戳，然后直接与时间戳字段进行比较。这样

created_at

登录后复制

字段就能继续使用其上的索引。

-- 查询2023年3月15日当天的数据
SELECT *
FROM your_table
WHERE created_at >= UNIX_TIMESTAMP('2023-03-15 00:00:00')
  AND created_at < UNIX_TIMESTAMP('2023-03-16 00:00:00');

登录后复制

或者，如果你想查询某个时间点之前或之后的数据：

SELECT *
FROM your_table
WHERE created_at < UNIX_TIMESTAMP('2023-03-15 12:00:00');

登录后复制

这种方式，通过将查询条件“逆向”转换为时间戳，巧妙地避开了对表字段使用函数导致索引失效的问题。这在我看来，是处理时间戳查询时最重要的一点。

MySQL中时间戳与日期转换的常用函数有哪些？

在MySQL中，处理时间戳和日期时间类型之间的转换，远不止

FROM_UNIXTIME

登录后复制

一个函数。实际上，它是一个函数族，各自扮演着不同的角色。理解这些函数能让你在数据处理时更加游刃有余。

我们刚刚聊了

FROM_UNIXTIME(unix_timestamp, [format])

登录后复制

，它负责把Unix时间戳转换为日期时间格式。它的兄弟函数是

UNIX_TIMESTAMP([date])

登录后复制

。这个函数做的事情正好相反：它将一个日期时间表达式（可以是

DATETIME

登录后复制

、

DATE

登录后复制

、

TIMESTAMP

登录后复制

类型的值，或者一个日期时间字符串）转换为Unix时间戳。当你需要将一个人类可读的日期转换成数据库能直接比较的整数时间戳时，

UNIX_TIMESTAMP()

登录后复制

就派上用场了。比如：

SELECT UNIX_TIMESTAMP('2023-03-15 10:30:00');
-- 结果：1678876200

登录后复制

除了这两个核心的转换函数，还有一些与日期时间格式化和解析紧密相关的函数也值得一提：

```
DATE_FORMAT(date, format)
```
登录后复制
: 这个函数用于将一个日期时间值格式化为指定格式的字符串。这在展示数据时非常有用，比如你只想显示日期部分，或者想以特定的顺序显示年月日。它和
```
FROM_UNIXTIME
```
登录后复制
的第二个参数格式字符串是通用的。
```
SELECT DATE_FORMAT('2023-03-15 10:30:00', '%Y年%m月%d日 %H点%i分');
-- 结果：'2023年03月15日 10点30分'
```
登录后复制
```
STR_TO_DATE(str, format)
```
登录后复制
: 这是
```
DATE_FORMAT
```
登录后复制
的反向操作。它将一个字符串按照指定的格式解析成一个日期时间值。当你从外部系统获取到格式不一的日期字符串，需要将其导入到MySQL的日期时间字段时，这个函数就显得尤为重要。
```
SELECT STR_TO_DATE('2023-03-15', '%Y-%m-%d');
-- 结果：'2023-03-15 00:00:00'
```
登录后复制
```
CONVERT_TZ(dt, from_tz, to_tz)
```
登录后复制
: 虽然不是直接的时间戳转换，但涉及到时间处理，它能将一个日期时间值从一个时区转换到另一个时区。这对于处理跨时区的业务数据非常关键，尤其是在全球化应用中。

理解并熟练运用这些函数，能让你在处理MySQL的时间数据时更加灵活和高效，避免许多常见的时间处理陷阱。

在WHERE子句中直接使用函数转换时间戳为什么可能影响查询性能？

这其实是一个非常经典的数据库性能优化问题，不单单针对MySQL的时间戳转换，而是所有在

WHERE

登录后复制

子句中对索引列进行函数操作的场景。当你对一个列应用了函数，比如

FROM_UNIXTIME(created_at, '%Y-%m-%d')

登录后复制

，数据库的查询优化器就无法直接利用

created_at

登录后复制

列上可能存在的B-tree索引了。

想象一下，数据库的索引就像一本书的目录。如果你要找“第十章”的内容，你可以直接翻到目录里对应的页码。但如果你的查询条件变成了“所有包含‘数据库优化’字样的章节”，那么目录就帮不上忙了，你不得不一页一页地翻遍整本书去查找。

回到我们的时间戳例子，如果

created_at

登录后复制

字段被索引了，并且你执行的是：

比格设计

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

124

查看详情

SELECT * FROM your_table WHERE created_at >= 1678886400 AND created_at < 1678972800;

登录后复制

数据库可以直接在

created_at

登录后复制

的索引树上进行范围查找，快速定位到符合条件的数据行。这个过程非常高效，尤其是在数据量巨大的表里。

然而，当你写成这样：

SELECT * FROM your_table WHERE FROM_UNIXTIME(created_at, '%Y-%m-%d') = '2023-03-15';

登录后复制

对于数据库来说，它无法预先知道

FROM_UNIXTIME(created_at, '%Y-%m-%d')

登录后复制

会产生什么值。为了评估这个条件，它不得不对表中的每一行数据都执行

FROM_UNIXTIME

登录后复制

函数，计算出结果，然后再与

'2023-03-15'

登录后复制

进行比较。这就意味着它需要扫描整个表，读取每一行数据，即使

created_at

登录后复制

字段有索引，这个索引也几乎形同虚设。这种操作被称为“全表扫描”（Full Table Scan），在生产环境中，尤其是在高并发或大数据量的场景下，全表扫描是性能杀手，会导致查询响应时间急剧增加，甚至拖垮整个数据库。

所以，核心的优化思路是：让函数作用于常量值，而不是表中的列。

将

WHERE

登录后复制

条件从

函数(列) = 值

登录后复制

转换为

列 运算符 函数(值)

登录后复制

。这样，列保持“干净”，索引就能被有效利用。这也就是为什么我之前会强调将日期常量转换为时间戳与时间戳字段进行比较的原因。

如何高效地在MySQL中查询特定日期范围的时间戳数据？

在MySQL中高效查询特定日期范围的时间戳数据，关键在于利用索引，并避免对时间戳字段本身应用函数。这里我会分享几种我常用的策略，它们都围绕着这个核心原则。

1. 使用

BETWEEN

登录后复制

和
UNIX_TIMESTAMP()
登录后复制
转换的日期范围

这是最常见且高效的方法。它通过将日期字符串转换为Unix时间戳，然后利用

BETWEEN

登录后复制

操作符来匹配时间戳范围。

-- 查询2023年3月15日全天的数据
SELECT *
FROM your_table
WHERE created_at BETWEEN UNIX_TIMESTAMP('2023-03-15 00:00:00') AND UNIX_TIMESTAMP('2023-03-15 23:59:59');

登录后复制

这种方式清晰明了，并且

created_at

登录后复制

字段上的索引能够得到充分利用。

2. 使用

>=

登录后复制

和
<
登录后复制
操作符精确匹配日期范围

有时，使用

>=

登录后复制

和

登录后复制

组合比

BETWEEN

登录后复制

更灵活，尤其是在处理日期边界时，可以避免一些潜在的精度问题（尽管对于整秒的时间戳通常不是大问题）。

-- 查询2023年3月15日全天的数据
SELECT *
FROM your_table
WHERE created_at >= UNIX_TIMESTAMP('2023-03-15 00:00:00')
  AND created_at < UNIX_TIMESTAMP('2023-03-16 00:00:00');

登录后复制

这种方法同样能有效利用

created_at

登录后复制

上的索引。我个人更倾向于这种写法，因为它更精确地定义了“当天”的范围，即从某天0点0分0秒开始，到第二天0点0分0秒之前。

3. 动态日期范围查询

在实际应用中，我们经常需要查询“最近7天”、“本月”等动态日期范围。这时，结合MySQL的日期函数来生成时间戳就显得非常方便。

查询最近7天的数据（包括今天）：

SELECT *
FROM your_table
WHERE created_at >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 7 DAY);
-- 如果需要精确到秒，可以这样：
-- WHERE created_at >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY))

登录后复制

这里

CURDATE()

登录后复制

获取当前日期（不含时间），

INTERVAL 7 DAY

登录后复制

是日期间隔。

查询本月的数据：

SELECT *
FROM your_table
WHERE created_at >= UNIX_TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-01'))
  AND created_at < UNIX_TIMESTAMP(DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH));

登录后复制