MySQL 日期和时间指南：数据类型、函数、格式化及范围查询

1. 引言

MySQL 是一种广泛用于构建 Web 应用程序和数据库的数据库管理系统，正确处理日期数据尤为重要。例如，日期相关数据出现在许多场景中：管理博客文章、跟踪产品销售历史以及存储用户登录历史。通过正确管理日期数据，您可以高效处理数据并向用户提供准确的信息。

在本指南中，我们将全面解释从 MySQL 中的基本日期/时间数据类型和如何使用日期函数，到基于日期的计算和范围查询的一切。此内容针对初学者到中级用户设计，我们将包含基于实际用例的实用查询示例——使其在日常工作中非常有用。

通过阅读本文，您将能够做到以下事项：

• 了解 MySQL 日期/时间数据类型的特性，并为您的数​​据选择合适的类型。
• 使用日期函数轻松检索和操作当前日期、过去日期和未来日期。
• 通过执行日期范围搜索和比较来提取特定时期的数据。

现在，让我们从下一节开始查看 MySQL 日期的基础知识。

2. MySQL 日期/时间数据类型的概述

在 MySQL 中管理日期时，根据数据和用例选择正确的日期/时间数据类型很重要。MySQL 提供了多种用于处理日期和时间的数​​据类型，每种类型都有不同的特性和用法。在本节中，我们将详细解释主要日期/时间类型及其典型用例。

DATE

DATE 类型仅存储日期（年、月、日），不包括时间组件。支持的范围是从“1000-01-01”到“9999-12-31”，因此可以处理从 1000 年到 9999 年的日期。它适用于时间无关的仅日期信息，例如生日或周年纪念日。

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

TIME 类型存储时间（小时、分钟、秒）。支持的范围是从“-838:59:59”到“838:59:59”。因为它可以表示负时间，因此也可以用于表达时间差异。它对于记录工作时间或任务持续时间很有用。

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

DATETIME 类型存储日期和时间。支持的范围是从“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。它不依赖于时区，您可以精确检索保存的值。它适用于记录过去事件或未来日程的时间戳。

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

TIMESTAMP 类型存储日期和时间，并根据服务器时区自动转换值。支持的范围是从“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”。它与 Unix 纪元时间兼容，适用于记录时间戳和更改历史。此外，因为 TIMESTAMP 可以默认设置为当前时间，因此方便自动记录创建和更新时间。

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

YEAR 类型仅存储年份。支持的范围是从“1901”到“2155”。当您想表示特定年份时很有用，例如制造年份或成立年份。

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

各种日期/时间类型的比较和用例

总结

MySQL 的日期/时间数据类型应根据您的数据特征以及计划的使用方式来选择。在下一节中，我们将更详细地探讨 DATETIME 和 TIMESTAMP 之间的差异，这两者特别容易混淆。

3. DATETIME 和 TIMESTAMP 的差异

在 MySQL 中处理日期和时间时，DATETIME 和 TIMESTAMP 是常用的类型，但它们之间存在重要差异。两者都存储年、月、日、小时、分钟和秒，但它们在处理时区和可存储的时间范围方面不同。您应根据使用场景来选择。在本节中，我们将详细解释 DATETIME 和 TIMESTAMP 的差异和特性。

DATETIME 的特性

• 不依赖时区：DATETIME 类型不受服务器时区设置的影响。您可以精确如保存时那样检索存储的值。
• 范围：支持从 1000-01-01 00:00:00 到 9999-12-31 23:59:59。
• 使用场景：适用于您希望不依赖任何特定时区存储的数据，例如历史事件或未来日程。

示例：存储事件日期/时间

例如，如果您希望保持“通用”的日期/时间值不变，DATETIME 是一个不错的选择。以下示例记录了一个特定日期和时间的计划事件。

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

在此表中，event_datetime 中存储的日期/时间不受时区影响，并且值会精确如存储时那样检索。

TIMESTAMP 的特性

• 依赖时区：TIMESTAMP 类型基于服务器时区进行存储和检索。当在不同时区之间的服务器之间移动数据时，会相应进行转换。
• 范围：支持从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC（基于 Unix 时间范围）。
• 自动更新支持：使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，MySQL 可以在插入或更新数据时自动记录当前时间戳。
• 使用场景：最适合自动跟踪创建或更新时间，例如当您希望记录更新时时间戳发生变化。

示例：存储帖子的创建和更新时间

此示例使用 TIMESTAMP 的自动更新功能来记录博客帖子的创建和更新时间。

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

通过此设置，created_at 在帖子首次创建时记录，而 updated_at 在每次编辑帖子时自动更新。

比较表：DATETIME 与 TIMESTAMP

如何选择

• 如果您不希望受时区影响，请选择 DATETIME：例如，如果您希望将事件时间作为特定国家/地区的固定本地时间存储，DATETIME 可能合适。
• 如果您需要自动更新或时区处理，请选择 TIMESTAMP：例如，如果您希望自动记录数据库行更新时间，TIMESTAMP 很方便。

总结

DATETIME 和 TIMESTAMP 具有不同的特性。通过为您的目的选择合适的类型，您可以更有效地管理日期/时间数据。在下一节中，我们将探讨 MySQL 中的基本日期函数。

4. MySQL 日期/时间函数基础

MySQL 提供了许多用于处理日期和时间的函数——从检索当前日期/时间到添加或减去间隔。这些函数在数据库管理和分析中非常有用。在本节中，我们将解释 MySQL 日期/时间函数的基础知识及其典型使用场景。

获取当前日期/时间的函数

MySQL 中用于获取当前日期和时间的三个常用函数是 NOW()、CURDATE() 和 CURTIME()。

NOW()

NOW() 函数返回当前日期和时间，格式为 YYYY-MM-DD HH:MM:SS。它适用于记录日志和时间戳记录。

SELECT NOW(); -- Get the current date and time

CURDATE()

CURDATE() 函数返回当前日期，格式为 YYYY-MM-DD。它不包含时间，因此适合仅需要日期的情况。

SELECT CURDATE(); -- Get the current date

CURTIME()

CURTIME() 函数返回当前时间，格式为 HH:MM:SS。在仅需要时间而不包含日期时使用它。

SELECT CURTIME(); -- Get the current time

用于添加/减去日期的函数

要向日期添加或减去间隔，请使用 DATE_ADD() 和 DATE_SUB()。这些函数可以轻松计算未来的或过去的日期。

DATE_ADD()

DATE_ADD() 函数向日期添加指定的间隔。例如，它适用于获取 7 天后或 1 个月后的日期。

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

DATE_SUB() 函数从日期中减去指定的间隔。使用它来计算过去的日期。

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

用于计算日期差异的函数

要计算两个日期之间的差异，DATEDIFF() 非常方便。例如，您可以计算从特定日期起经过了多少天，或两个日期之间的天数。

DATEDIFF()

DATEDIFF() 函数返回两个日期之间的差异，以天为单位。它适用于确认从开始日期到结束日期的天数。

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

其他有用的日期函数

MySQL 提供了其他有用的日期函数。

EXTRACT()

EXTRACT() 函数从日期中提取特定部分（年、月、日等）。在仅需要日期的一部分时，它非常有用。

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

DATE_FORMAT() 函数以指定的格式显示日期。在希望以特定格式显示日期时（例如，日式格式）使用它。

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. 日期格式化和转换方法

在 MySQL 中，您可以将日期显示方式更改为更易读的格式，或将日期字符串转换为日期类型。这允许您灵活控制显示格式，并在输入格式不同时一致管理数据。在本节中，我们将解释用于日期格式化和转换的主要函数。

使用 DATE_FORMAT() 函数进行日期格式化

使用 DATE_FORMAT()，您可以以指定的格式显示日期值。这在需要标准格式以外的显示格式时特别有用，例如日式“YYYY年MM月DD日”格式。

格式选项

使用 DATE_FORMAT()，您可以使用以下格式选项：

• %Y : 4 位年份
• %y : 2 位年份
• %m : 2 位月份 (01–12)
• %d : 2 位日期 (01–31)
• %H : 2 位小时 (00–23)
• %i : 2 位分钟 (00–59)
• %s : 2 位秒 (00–59)

示例用法

例如，要将日期 2023-01-01 显示为“2023年01月01日”，您可以编写以下内容：

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

您也可以使用斜杠显示日期和时间，例如“2023/01/01 12:30:45”。

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

将字符串转换为日期使用 STR_TO_DATE()

STR_TO_DATE() 函数将日期字符串转换为 MySQL 日期类型。例如，当你想把类似 “2023年01月01日” 的字符串视为 DATE 值时，它非常有用。

示例用法

要将字符串 “2023-01-01” 转换为 DATE 值，请编写如下代码：

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

要将日式字符串如 “2023年01月01日” 转换为日期，需要指定相应的格式：

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

不同格式日期转换示例

在实际使用中，日期输入格式可能各不相同，因此需要将其转换为统一格式。以下是几个示例。

1. 将 “YYYY/MM/DD” 转换为 DATE 值

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. 将 “MM-DD-YYYY” 转换为 DATE 值

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

这样，即使数据以不同格式录入，也能统一存储和管理为统一的日期格式。

DATE_FORMAT 与 STR_TO_DATE 的区别以及何时使用

• DATE_FORMAT()：用于更改已有日期值的显示格式。由于仅影响显示，存储的数据本身不会被修改。
• STR_TO_DATE()：用于将日期字符串转换为 MySQL 的 DATE 或 DATETIME 值。这会改变存储的数据类型，便于使用其他 MySQL 函数和查询处理日期。

小结

通过使用 DATE_FORMAT() 和 STR_TO_DATE()，可以灵活地管理日期数据的显示方式和存储方式。这使得在保持日期管理一致性的同时，能够更灵活地处理用户或系统的输入。下一节我们将介绍日期计算和比较，并深入探讨如何使用日期数据进行周期的计算和比较。

6. 日期计算与比较

MySQL 提供了多种便捷的日期计算和比较函数，可用于提取特定时间段的数据或计算日期差异以进行分析。本节将介绍用于日期计算和比较的主要函数及其使用方法。

计算日期差异的函数：DATEDIFF()

DATEDIFF() 函数返回两个日期之间的“天数”差异。当你想检查两个日期之间相隔多少天，或计算从过去某事件到今天已过去多少天时，它非常有用。

示例用法

例如，要计算 2023 年 1 月 1 日到 2023 年 1 月 10 日之间的天数，请编写如下代码：

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

在本例中，起始日期到结束日期相差 9 天，结果为 9。

按单位计算差异：TIMESTAMPDIFF()

TIMESTAMPDIFF() 函数按指定单位（年、月、日、小时、分钟、秒）计算两个日期或日期时间之间的差异。例如，你可以根据需要计算“月”或“小时”等差异。

示例用法

1. 计算月份差异

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. 计算小时差异

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

日期比较

日期比较使用 MySQL 的标准比较运算符（<, >, <=, >=, =）进行。这使得可以提取特定时间段的数据或将过去/未来的日期作为条件。

示例用法

例如，要提取“2023 年 1 月 1 日及之后”的数据，请编写如下代码：

SELECT * FROM events WHERE event_date >= '2023-01-01';

使用 BETWEEN 指定范围

BETWEEN 运算符允许您指定日期范围来检索数据。当您需要提取落在特定时间段内的记录时，它非常有用。

示例用法

例如，要检索 2023 年 1 月 1 日至 2023 年 1 月 31 日之间的数据，请编写如下代码：

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

使用 ADDDATE() 和 SUBDATE() 进行日期计算

您可以使用 ADDDATE() 和 SUBDATE() 对日期增加或减少一定天数。这在计算未来或过去的日期时非常方便。

示例用法

1. 在日期上加 10 天

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. 在日期上减 10 天

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

小结

通过使用 MySQL 的日期计算和比较功能，您可以高效地提取特定时期的数据，并按时间范围分析数据。在下一节中，我们将更深入地探讨“日期范围搜索”这一更高级的主题。