如何在MySQL中操作日期和时间/MySQL日期和时间函数详解|极客笔记

如何在MySQL中操作日期和时间/MySQL日期和时间函数详解

在数据库中，日期和时间是非常常见的数据类型，处理好日期和时间数据对于数据的准确性和可靠性至关重要。MySQL 是一个流行的关系型数据库管理系统，它提供了一系列强大的函数和操作符来处理日期和时间数据。本文将详细介绍在MySQL中如何操作日期和时间，包括日期和时间函数的使用，日期和时间的格式化和转换，以及常见的日期和时间操作实例。

日期和时间数据类型

在MySQL中，日期和时间数据通常使用以下几种数据类型来存储：

DATE：用于存储日期，格式为 ‘YYYY-MM-DD’
TIME：用于存储时间，格式为 ‘HH:MM:SS’
DATETIME：用于存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’
TIMESTAMP：用于存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’，但是存储范围更广，可以存储更早的日期

这些数据类型在MySQL中存储日期和时间数据的方式略有不同，选择合适的数据类型取决于具体的业务需求。在使用日期和时间函数时，需要根据数据类型的不同来选择合适的函数。

日期和时间函数

MySQL提供了丰富的日期和时间函数，用于处理和操作日期和时间数据。下面列举了一些常用的日期和时间函数：

NOW()

NOW() 函数返回当前的日期和时间。可以直接调用这个函数获取当前的日期时间。

SELECT NOW();

-- 结果示例：2022-01-01 12:34:56

DATE_FORMAT(date,format)

DATE_FORMAT() 函数用于将日期或时间数据格式化为指定的格式。format参数是一个字符串，可以自定义日期时间的显示格式。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

-- 结果示例：2022-01-01

DATE_ADD(date,INTERVAL n type)

DATE_ADD() 函数用于在日期上添加指定的时间间隔。第一个参数是日期，第二个参数是要添加的时间间隔，第三个参数是时间间隔的单位（例如DAY、MONTH、YEAR等）。

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

-- 结果示例：2022-01-02 12:34:56

CURDATE()

CURDATE() 函数返回当前的日期，不包含时间信息。

SELECT CURDATE();

-- 结果示例：2022-01-01

DAYNAME(date)

DAYNAME() 函数返回指定日期的星期名称。

SELECT DAYNAME('2022-01-01');

-- 结果示例：Saturday

WEEKDAY(date)

WEEKDAY() 函数返回指定日期是星期几，返回值范围为 0（星期一）到 6（星期日）。

SELECT WEEKDAY('2022-01-01');

-- 结果示例：6

DATEDIFF(date1, date2)

DATEDIFF() 函数用于计算两个日期之间的天数差。

SELECT DATEDIFF('2022-01-01', '2021-12-25');

-- 结果示例：7

DATE_SUB(date,INTERVAL n type)

DATE_SUB() 函数用于在日期上减去指定的时间间隔。参数和 DATE_ADD() 函数类似。

SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- 结果示例：2021-12-01 12:34:56

TIMESTAMPDIFF(unit,datetime1,datetime2)

TIMESTAMPDIFF() 函数用于计算两个日期时间之间的时间差，单位可以是秒、分、时、天等。

SELECT TIMESTAMPDIFF(MINUTE, '2022-01-01 12:00:00', '2022-01-01 12:30:00');

-- 结果示例：30

UNIX_TIMESTAMP()

UNIX_TIMESTAMP() 函数将日期时间转换为 UNIX 时间戳。

SELECT UNIX_TIMESTAMP('2022-01-01 12:30:00');

-- 结果示例：1641024600

日期和时间的格式化和转换

在MySQL中，日期和时间的格式化是很常见的操作，可以通过 DATE_FORMAT() 函数将日期时间数据格式化为指定的格式。下面是一些常见的日期时间格式化：

%Y：年份
%m：月份
%d：日期
%H：小时（24小时制）
%i：分钟
%s：秒

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

-- 结果示例：2022-01-01 12:34:56

另外，也可以使用 STR_TO_DATE() 函数将字符串转换为日期时间类型。

SELECT STR_TO_DATE('2022-01-01', '%Y-%m-%d');

-- 结果示例：2022-01-01 00:00:00

常见的日期和时间操作实例

查询指定日期范围的数据

有时候我们需要查询在指定日期范围内的数据，可以使用 BETWEEN 操作符来实现。

SELECT * FROM table_name WHERE date_column BETWEEN '2022-01-01' AND '2022-01-31';

统计每个月的销售额

可以使用 MONTH() 函数和 GROUP BY 子句来统计每个月的销售额。

SELECT MONTH(sales_date), SUM(sales_amount) FROM sales_table GROUP BY MONTH(sales_date);

计算某个日期之后的第一个周一是哪天

可以使用 WEEKDAY() 函数和 CASE 表达式来计算某个日期之后的第一个周一。

SELECT CASE
    WHEN WEEKDAY('2022-01-01') < 1 THEN DATE_ADD('2022-01-01', INTERVAL 1 DAY)
    WHEN WEEKDAY('2022-01-01') = 1 THEN '2022-01-01'
    ELSE DATE_ADD('2022-01-01', INTERVAL (7 - WEEKDAY('2022-01-01')) DAY)
    END AS first_monday_after_date;