发布于 由admin

MySQL高级函数之：`DATE_FORMAT()`：其在日期格式化中的高级用法。

MySQL高级函数之：DATE_FORMAT()：其在日期格式化中的高级用法

大家好，今天我们来深入探讨 MySQL 中一个非常实用且功能强大的日期函数：DATE_FORMAT()。这个函数允许我们根据自定义的格式字符串将日期和时间值转换为字符串，从而满足各种各样的显示和数据处理需求。虽然它的基本用法相对简单，但通过组合不同的格式化符号，我们可以实现非常复杂的日期格式化。

1. DATE_FORMAT() 函数的基本语法

DATE_FORMAT() 函数的语法如下：

DATE_FORMAT(date, format)
  • date: 要格式化的日期或时间值。它可以是一个 DATE, DATETIME, TIMESTAMP 类型的值，也可以是能够转换为这些类型的字符串。
  • format: 一个字符串，包含用于指定输出格式的格式化符号。这是控制 DATE_FORMAT() 函数行为的关键部分。

2. 常用的格式化符号

format 字符串中的格式化符号以百分号 (%) 开头，后面跟着一个字符，用于指定要提取或格式化的日期/时间部分。以下是一些常用的格式化符号及其含义：

格式化符号 含义 示例
%Y 四位数的年份 (例如: 2023) 2023
%y 两位数的年份 (例如: 23) 23
%m 两位数的月份 (01-12) 08
%c 月份 (1-12) 8
%d 两位数的日 (01-31) 20
%e 日 (1-31) 20
%H 24小时制的小时 (00-23) 14
%k 24小时制的小时 (0-23) 14
%h 12小时制的小时 (01-12) 02
%I 12小时制的小时 (01-12) 02
%l 12小时制的小时 (1-12) 2
%i 分钟 (00-59) 30
%s 秒 (00-59) 45
%S 秒 (00-59) 45
%p AM 或 PM PM
%r 12小时制的时间, 包括 AM/PM (hh:mm:ss AM/PM) 02:30:45 PM
%T 24小时制的时间 (hh:mm:ss) 14:30:45
%w 星期几 (0=Sunday, 1=Monday, …, 6=Saturday) 3
%W 星期几的完整名称 (Sunday, Monday, …, Saturday) Wednesday
%a 星期几的缩写 (Sun, Mon, …, Sat) Wed
%b 月份的缩写 (Jan, Feb, …, Dec) Aug
%M 月份的完整名称 (January, February, …, December) August
%j 一年中的第几天 (001-366) 232
%U 一年中的第几周 (00-53), 周日是每周的第一天 33
%u 一年中的第几周 (00-53), 周一是每周的第一天 33
%D 带有英语后缀的日 (1st, 2nd, 3rd, …) 20th
%% 字面意义上的百分号 (%) %

3. 实践案例：基本格式化

假设我们有一个名为 orders 的表，其中包含一个名为 order_date 的 DATETIME 类型的列。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATETIME
);

INSERT INTO orders (order_date) VALUES
('2023-08-20 14:30:45'),
('2023-08-21 09:15:20'),
('2023-08-22 18:00:00');

现在，我们可以使用 DATE_FORMAT() 来格式化 order_date 列：

  • 只显示日期 (YYYY-MM-DD):

    SELECT order_id, DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

    结果：

    order_id formatted_date
    1 2023-08-20
    2 2023-08-21
    3 2023-08-22

  • 显示日期 (MM/DD/YYYY):

    SELECT order_id, DATE_FORMAT(order_date, '%m/%d/%Y') AS formatted_date
FROM orders;

    结果：

    order_id formatted_date
    1 08/20/2023
    2 08/21/2023
    3 08/22/2023

  • 显示日期和时间 (YYYY-MM-DD HH:MM:SS):

    SELECT order_id, DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS formatted_datetime
FROM orders;

    结果：

    order_id formatted_datetime
    1 2023-08-20 14:30:45
    2 2023-08-21 09:15:20
    3 2023-08-22 18:00:00

4. 实践案例：更高级的格式化

DATE_FORMAT() 的强大之处在于它可以组合多种格式化符号，创建定制化的日期字符串。

  • 显示带有星期几和月份名称的日期:

    SELECT order_id, DATE_FORMAT(order_date, '%W, %M %e, %Y') AS formatted_date
FROM orders;

    结果：

    order_id formatted_date
    1 Sunday, August 20, 2023
    2 Monday, August 21, 2023
    3 Tuesday, August 22, 2023

  • 显示带有英语后缀的日期:

    SELECT order_id, DATE_FORMAT(order_date, '%M %D, %Y') AS formatted_date
FROM orders;

    结果：

    order_id formatted_date
    1 August 20th, 2023
    2 August 21st, 2023
    3 August 22nd, 2023

  • 显示 12 小时制时间:

    SELECT order_id, DATE_FORMAT(order_date, '%h:%i %p') AS formatted_time
FROM orders;

    结果：

    order_id formatted_time
    1 02:30 PM
    2 09:15 AM
    3 06:00 PM

  • 将日期格式化为 UNIX 时间戳 (虽然不是直接用 DATE_FORMAT，但经常一起使用):

    SELECT order_id, UNIX_TIMESTAMP(order_date) AS unix_timestamp
FROM orders;

    结果 (近似值，会随时间变化):

    order_id unix_timestamp
    1 1692541845
    2 1692503720
    3 1692573600

    反过来，也可以将 UNIX 时间戳格式化为日期：

    SELECT FROM_UNIXTIME(1692541845, '%Y-%m-%d %H:%i:%s');

    结果：

    FROM_UNIXTIME(1692541845, ‘%Y-%m-%d %H:%i:%s’)
    2023-08-20 14:30:45

5. DATE_FORMAT() 在实际应用中的场景

DATE_FORMAT() 在各种实际应用中都非常有用：

  • 数据报表: 生成各种格式的日期和时间数据，用于生成易于阅读的报表。
  • 用户界面: 以用户友好的方式显示日期和时间。 例如，将日期显示为 "August 20th, 2023" 而不是 "2023-08-20"。
  • 数据导出: 将日期和时间数据导出为特定格式的文件 (例如 CSV, Excel)。
  • 数据清洗: 将不一致的日期格式统一为标准格式。
  • 日志分析: 分析日志文件中的日期和时间信息。
  • 事件调度: 根据特定格式的日期和时间字符串来安排任务。

6. DATE_FORMAT() 与 STR_TO_DATE() 的配合使用

DATE_FORMAT() 用于将日期格式化为字符串，而 STR_TO_DATE() 则相反，用于将字符串转换为日期。这两个函数经常一起使用，以实现日期格式的转换和处理。

例如，假设我们有一个包含日期字符串的表，但日期格式不统一：

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_date_string VARCHAR(50)
);

INSERT INTO events (event_date_string) VALUES
('2023-08-20'),
('08/21/2023'),
('August 22, 2023');

我们可以使用 STR_TO_DATE() 将这些字符串转换为 DATE 类型，然后再使用 DATE_FORMAT() 将它们格式化为统一的格式：

SELECT
    event_id,
    event_date_string,
    DATE_FORMAT(
        CASE
            WHEN event_date_string LIKE '%-%' THEN STR_TO_DATE(event_date_string, '%Y-%m-%d')
            WHEN event_date_string LIKE '%/%' THEN STR_TO_DATE(event_date_string, '%m/%d/%Y')
            ELSE STR_TO_DATE(event_date_string, '%M %d, %Y')
        END,
        '%Y-%m-%d'
    ) AS formatted_date
FROM events;

在这个例子中，我们使用 CASE 语句来判断日期字符串的格式，然后使用相应的 STR_TO_DATE() 格式字符串将其转换为 DATE 类型。最后，我们使用 DATE_FORMAT() 将 DATE 类型的值格式化为 YYYY-MM-DD 格式。

结果：

event_id event_date_string formatted_date
1 2023-08-20 2023-08-20
2 08/21/2023 2023-08-21
3 August 22, 2023 2023-08-22

7. 注意事项

  • DATE_FORMAT() 的 format 字符串区分大小写。
  • 如果 date 参数为 NULL，则 DATE_FORMAT() 返回 NULL。
  • 确保 format 字符串与 date 参数的实际格式匹配，否则可能返回不正确的结果或 NULL。 在使用STR_TO_DATE() 时尤其要注意这点。
  • DATE_FORMAT() 返回的是字符串类型，不是日期类型。

8. 性能考量

虽然 DATE_FORMAT() 非常方便，但在处理大量数据时，需要考虑其性能影响。 频繁地在查询中使用 DATE_FORMAT() 可能会导致性能下降，因为它会阻止 MySQL 使用索引。 如果需要对格式化后的日期进行排序或过滤，最好先将数据转换为 DATE 或 DATETIME 类型，然后再进行格式化。 此外，尽量避免在 WHERE 子句中使用 DATE_FORMAT()，特别是当该列上有索引时。

9. 其他相关的日期函数

除了 DATE_FORMAT() 和 STR_TO_DATE() 之外，MySQL 还提供了许多其他的日期函数，可以用于执行各种日期和时间操作。 一些常用的函数包括：

  • NOW(): 返回当前日期和时间。
  • CURDATE(): 返回当前日期。
  • CURTIME(): 返回当前时间。
  • DATE(): 从 DATETIME 或 TIMESTAMP 值中提取日期部分。
  • TIME(): 从 DATETIME 或 TIMESTAMP 值中提取时间部分。
  • YEAR(), MONTH(), DAY(): 从日期中提取年、月和日。
  • HOUR(), MINUTE(), SECOND(): 从时间中提取小时、分钟和秒。
  • DATE_ADD(), DATE_SUB(): 添加或减去日期或时间间隔。
  • DATEDIFF(): 计算两个日期之间的天数差。
  • TIMESTAMPDIFF(): 计算两个日期或时间之间的差，可以指定不同的时间单位。

这些函数可以与 DATE_FORMAT() 结合使用，以实现更复杂的日期和时间处理。

日期格式化，在报表和数据处理中不可或缺

总而言之，DATE_FORMAT() 是 MySQL 中一个功能强大且灵活的日期函数，可以用于将日期和时间值格式化为各种字符串格式。 掌握 DATE_FORMAT() 的使用方法，可以帮助我们更好地处理和展示日期数据，满足各种不同的应用需求。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注