MySQL 当前时间指南：NOW()、CURRENT_TIMESTAMP、DATE_FORMAT、时区设置与最佳实践

1. 介绍

何时需要在 MySQL 中处理当前时间？

在 MySQL 中，检索当前时间在许多不同的场景下都是必需的。例如，以下用例很常见：

• 在注册数据时自动插入时间戳
• 例如，在保存订单数据或日志数据时记录创建日期和时间。
• 基于当前时间过滤数据
• 例如，仅检索过去 7 天的数据或搜索具有未来日期的记录。
• 为显示格式化日期和时间
• 在生成报告时，将日期和时间值格式化以提高可读性。
• 使用当前时间管理数据过期
• 例如，通过将优惠券与当前时间比较来确定其是否仍然有效。

正如您所见，正确检索和操作 MySQL 中的当前时间是数据库管理中的一项重要技能。

本文将学习的内容

本文将详细解释以下主题：

• 如何在 MySQL 中检索当前时间（NOW()、CURRENT_TIMESTAMP 等）
• 如何更改日期和时间格式（使用 DATE_FORMAT()）
• 使用当前时间进行日期和时间计算（使用 INTERVAL）
• 如何更改时区（SET SESSION time_zone）
• 将当前时间用作默认值（CURRENT_TIMESTAMP）
• 常见错误及其解决方法（FAQ）

从 MySQL 中处理“当前时间”的基础到高级用法，本指南提供了实用的 SQL 示例。请务必阅读至文末。

2. 如何在 MySQL 中检索当前时间

检索 MySQL 当前时间的函数列表

MySQL 提供了多个函数来检索当前时间。了解它们之间的差异并适当使用它们。

NOW() 函数

NOW() 是 MySQL 中最常用的检索当前时间的函数。
它同时返回日期和时间。

SELECT NOW();

输出示例：

2025-02-11 16:00:00

• NOW() 返回当前系统时间。
• 由于受时区影响，显示的时间可能因环境而异（在“时区设置”章节中有详细说明）。

如何使用 CURRENT_TIMESTAMP

CURRENT_TIMESTAMP 的行为几乎与 NOW() 相同。它符合 SQL 标准，也可在其他数据库中使用。

SELECT CURRENT_TIMESTAMP;

输出示例：

2025-02-11 16:00:00

使用 CURDATE() 获取仅日期

CURDATE() 用于在只想检索当前日期（年、月、日）时使用。

SELECT CURDATE();

输出示例：

2025-02-11

使用 CURTIME() 获取仅时间

如果只想检索当前时间（时、分、秒），请使用 CURTIME()。

SELECT CURTIME();

输出示例：

16:00:00

应该使用哪个函数？

3. 如何在 MySQL 中格式化当前时间

使用 DATE_FORMAT() 自定义格式化

DATE_FORMAT() 的基本语法

在 MySQL 中，您可以使用 DATE_FORMAT() 函数自由更改日期和时间的格式。

SELECT DATE_FORMAT(datetime_value, 'format_specifiers');

示例： 将 NOW() 转换为 YYYY/MM/DD HH:MM 格式

SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i');

输出：

2025/02/11 16:45

常用格式说明符列表

使用 TIME() 提取仅时间部分

如果想从 NOW() 返回的 datetime 中提取 仅时间部分，请使用 TIME() 函数。

SELECT TIME(NOW());

输出：

16:45:30

使用 YEAR()、MONTH() 和 DAY() 提取部分

要提取特定的部分，请使用以下函数。

格式化的实用示例

The following SQL is useful for trying various formats in practice.

SELECT 
    NOW() AS 'Original datetime',
    DATE_FORMAT(NOW(), '%Y/%m/%d') AS 'YYYY/MM/DD format',
    DATE_FORMAT(NOW(), '%H:%i:%s') AS 'HH:MM:SS',
    TIME(NOW()) AS 'Time only',
    YEAR(NOW()) AS 'Year',
    MONTH(NOW()) AS 'Month',
    DAY(NOW()) AS 'Day';

4. Date/Time Calculations Using the Current Time in MySQL

Add/Subtract with INTERVAL

Basic Syntax

SELECT current_datetime + INTERVAL number unit;
SELECT current_datetime - INTERVAL number unit;

Add Time Based on NOW()

For example, to retrieve the datetime “one week later”:

SELECT NOW() + INTERVAL 7 DAY;

Output example:

2025-02-18 16:30:00

Calculate the Difference Between Two Dates with DATEDIFF()

SELECT DATEDIFF(NOW(), '2025-01-01');

Output example:

30

Filter by a Date Range with BETWEEN

SELECT * FROM orders
WHERE created_at BETWEEN '2025-02-01 00:00:00' AND '2025-02-28 23:59:59';

5. MySQL Time Zone Settings

Check the Current Time Zone

SHOW VARIABLES LIKE '%time_zone%';

Output example:

+------------------+----------------+
| Variable_name    | Value          |
+------------------+----------------+
| system_time_zone | UTC            |
| time_zone        | SYSTEM         |
+------------------+----------------+

Change the Time Zone per Session

SET SESSION time_zone = 'Asia/Tokyo';

Change the Server’s Default Time Zone

Add the following to the configuration file (my.cnf):

[mysqld]
default_time_zone = 'Asia/Tokyo'

Get UTC Time with UTC_TIMESTAMP

SELECT UTC_TIMESTAMP();

Convert to Local Time with CONVERT_TZ()

SELECT CONVERT_TZ(UTC_TIMESTAMP(), 'UTC', 'Asia/Tokyo');

6. How to Set the Current Time as a Default Value in MySQL

Set CURRENT_TIMESTAMP as the Default Value

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Automatic Updates with ON UPDATE CURRENT_TIMESTAMP

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Difference Between DATETIME and TIMESTAMP

Why You Cannot Use NOW() as a Default Value and the Solution

ERROR 1067 (42000): Invalid default value for 'created_at'

Solution:

CREATE TRIGGER set_created_at
BEFORE INSERT ON logs
FOR EACH ROW
SET NEW.created_at = NOW();

7. Common MySQL Errors and Solutions (FAQ)

Cannot Use NOW() as a Default Value

Error Example

CREATE TABLE logs (
    created_at DATETIME DEFAULT NOW()
);

ERROR 1067 (42000): Invalid default value for 'created_at'

Solution

CREATE TABLE logs (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CURRENT_TIMESTAMP Time Is Incorrect

SHOW VARIABLES LIKE 'time_zone';

Solution

SET SESSION time_zone = 'Asia/Tokyo';

NOW() Result Is Off by One Hour

SHOW VARIABLES LIKE 'system_time_zone';

Solution

SET GLOBAL time_zone = 'Asia/Tokyo';

BETWEEN Range Filtering Does Not Work as Expected

SELECT * FROM orders
WHERE created_at BETWEEN '2025-02-01' AND '2025-02-28';

Solution

SELECT * FROM orders
WHERE created_at BETWEEN '2025-02-01 00:00:00' AND '2025-02-28 23:59:59';

8. Best Practices for Handling the Current Time in MySQL

When to Use NOW() vs CURRENT_TIMESTAMP

When to Use TIMESTAMP vs DATETIME

Store in UTC and Convert to Local Time

SELECT CONVERT_TZ(event_time, 'UTC', 'Asia/Tokyo');

Use >= and < Instead of BETWEEN

SELECT * FROM orders
WHERE created_at >= '2025-02-01 00:00:00' 
AND created_at < '2025-03-01 00:00:00';

Standardize the Usage of INTERVAL

SELECT NOW() + INTERVAL 1 DAY;

Properly Clean Up Old Data

DELETE FROM logs WHERE created_at < NOW() - INTERVAL 1 YEAR LIMIT 1000;