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

要将时间戳转换为“年-月-日 时:分:秒”格式并计算两个日期间隔天数，可使用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

: 一个字符串，指定输出的格式。

下面是一些常用的格式化符号：

%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 时间。 然后在展示给用户时，根据用户的时区进行转换。 许多数据库系统都提供了时区转换函数。

CONVERT_TZ(dt, from_tz, to_tz)

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

AT TIME ZONE

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

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