日期函数实战：如何用DATE_FORMAT转换时间格式？计算日期差的常用方法-mysql教程-PHP中文网

日期函数实战：如何用DATE_FORMAT转换时间格式？计算日期差的常用方法

爱谁谁

发布： 2025-06-28 08:19:02

原创

536人浏览过

要将时间戳转换为“年-月-日时:分:秒”格式并计算两个日期间隔天数，可使用date_format函数和日期差函数。1. 使用date_format(date, format)将日期格式化为所需字符串，如'%y-%m-%d %h:%i:%s'用于完整时间格式；2. 不同数据库使用不同函数计算日期差，mysql用datediff(date1, date2)计算天数差、timestampdiff(unit, datetime1, datetime2)计算更精确的时间差，postgresql用减法操作符配合extract提取间隔部分，sql server用datediff(datepart, startdate, enddate)；3. 处理时区转换时，mysql用convert_tz(dt, from_tz, to_tz)，postgresql用at time zone，sql server用todatetimeoffset和convert组合；4. 避免在where子句中对日期列使用函数导致索引失效，推荐使用日期范围查询；5. 对空日期可用coalesce替换默认值，无效日期需在应用层或通过数据库设置处理。

日期函数实战：如何用DATE_FORMAT转换时间格式？计算日期差的常用方法

想把数据库里存储的时间戳变成易读的“年-月-日时:分:秒”格式？或者想知道两个日期之间隔了多少天？DATE_FORMAT 和日期差函数就是你的好帮手。

DATE_FORMAT 函数能帮你把日期时间值转换成各种你想要的字符串格式，而日期差函数则可以轻松计算两个日期之间的间隔。

解决方案

DATE_FORMAT 的用法：

DATE_FORMAT(date, format) 函数接受两个参数：
- date: 你要格式化的日期时间值，可以是一个日期时间类型的列，也可以是一个日期时间字面量。
- format: 一个字符串，指定输出的格式。
下面是一些常用的格式化符号：
- %Y: 四位年份 (例如: 2023)
- %y: 两位年份 (例如: 23)
- %m: 两位月份 (01-12)
- %d: 两位日 (01-31)
- %H: 24小时制小时 (00-23)
- %h: 12小时制小时 (01-12)
- %i: 分钟 (00-59)
- %s: 秒 (00-59)
举个例子，假设你有一个名为 created_at 的列，存储了用户创建账户的时间，你想把它格式化成 YYYY-MM-DD HH:MM:SS 的形式，你可以这样写：
```
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS formatted_date FROM users;
```
登录后复制
如果你只需要日期部分，可以这样写：
```
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS formatted_date FROM users;
```
登录后复制
或者，想要显示月份的英文缩写，可以这样：
```
SELECT DATE_FORMAT(created_at, '%Y-%b-%d') AS formatted_date FROM users; -- 例如: 2023-Oct-26
```
登录后复制
计算日期差的常用方法：

不同的数据库系统提供了不同的函数来计算日期差。
- MySQL:
  
  MySQL 提供了 DATEDIFF(date1, date2) 函数，计算 date1 - date2 的天数差。
```
SELECT DATEDIFF('2023-10-27', '2023-10-20') AS days_difference; -- 结果是 7
```
  登录后复制
  如果你想计算更精确的日期差，例如小时、分钟或秒，可以使用 TIMESTAMPDIFF(unit, datetime1, datetime2) 函数。 unit 参数指定了时间单位，例如 HOUR, MINUTE, SECOND。
```
SELECT TIMESTAMPDIFF(HOUR, '2023-10-27 10:00:00', '2023-10-27 12:30:00') AS hours_difference; -- 结果是 2
```
  登录后复制
- PostgreSQL:
  
  PostgreSQL 可以直接用减法操作符 - 来计算日期差，结果是一个 interval 类型的值。然后可以使用 EXTRACT 函数提取 interval 的不同部分。
```
SELECT '2023-10-27'::date - '2023-10-20'::date AS date_difference; -- 结果是 7 days
SELECT EXTRACT(DAY FROM ('2023-10-27'::date - '2023-10-20'::date)) AS days_difference; -- 结果是 7
```
  登录后复制
  对于更精确的时间差，同样可以使用减法操作符，然后提取需要的部分：
```
SELECT EXTRACT(HOUR FROM ('2023-10-27 12:30:00'::timestamp - '2023-10-27 10:00:00'::timestamp)) AS hours_difference; -- 结果是 2
SELECT EXTRACT(MINUTE FROM ('2023-10-27 12:30:00'::timestamp - '2023-10-27 10:00:00'::timestamp)) AS minutes_difference; -- 结果是 30
```
  登录后复制
- SQL Server:
  
  SQL Server 提供了 DATEDIFF(datepart, startdate, enddate) 函数，其中 datepart 指定了时间单位，例如 day, hour, minute。
```
SELECT DATEDIFF(day, '2023-10-20', '2023-10-27') AS days_difference; -- 结果是 7
SELECT DATEDIFF(hour, '2023-10-27 10:00:00', '2023-10-27 12:30:00') AS hours_difference; -- 结果是 2
```
  登录后复制

如何处理不同时区的时间？

在处理跨时区的日期时间数据时，你需要特别注意。首先，确保你的数据库存储的是 UTC 时间。然后在展示给用户时，根据用户的时区进行转换。许多数据库系统都提供了时区转换函数。

MySQL: 可以使用 CONVERT_TZ(dt, from_tz, to_tz) 函数进行时区转换。但是，你需要确保你的 MySQL 服务器配置了正确的时区信息。
```
SELECT CONVERT_TZ('2023-10-27 10:00:00', 'UTC', 'America/Los_Angeles') AS los_angeles_time;
```
登录后复制

PostgreSQL: 可以使用 AT TIME ZONE 操作符进行时区转换。

SELECT '2023-10-27 10:00:00 UTC'::timestamp AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time;

登录后复制

SQL Server: 可以使用 TODATETIMEOFFSET 和 CONVERT 函数进行时区转换。

SELECT CONVERT(DATETIME, TODATETIMEOFFSET('2023-10-27 10:00:00 UTC', 'America/Los_Angeles')) AS los_angeles_time;

登录后复制

性能优化：日期函数应该在 WHERE 子句中使用吗？

尽量避免在 WHERE 子句中对日期时间列使用函数，这会导致索引失效，从而降低查询性能。例如，不要这样写：

SELECT * FROM orders WHERE DATE_FORMAT(order_date, '%Y-%m') = '2023-10';  -- 不推荐

登录后复制

正确的做法是，直接使用日期时间范围进行查询：

SELECT * FROM orders WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01';  -- 推荐

登录后复制

如果你的业务场景确实需要在 WHERE 子句中使用函数，可以考虑创建一个函数索引，但这会增加数据库的维护成本。

如何处理空日期或无效日期？

在实际应用中，你可能会遇到空日期 (NULL) 或无效日期 (例如 '0000-00-00')。对于空日期，你可以使用 COALESCE 函数将其替换为一个默认值。

SELECT DATE_FORMAT(COALESCE(created_at, '1970-01-01'), '%Y-%m-%d') AS formatted_date FROM users;

登录后复制

对于无效日期，你需要在应用程序层面进行处理，或者使用数据库提供的函数进行校验。例如，在 MySQL 中，你可以设置 sql_mode 来禁止插入无效日期。

以上就是日期函数实战：如何用DATE_FORMAT转换时间格式？计算日期差的常用方法的详细内容，更多请关注php中文网其它相关文章！