mysql常用的日期函数以及查询昨天，今天，本周，上个月的数据

一 常用的日期函数

1. CURDATE()

CURDATE() 是一个 MySQL 函数，用于返回当前的日期，格式为 YYYY-MM-DD。它不包含时间部分，仅显示日期。

SELECT CURDATE();   // 2024-09-20

注意事项

• CURDATE() 返回的日期格式与系统的日期格式设置有关，但通常以 YYYY-MM-DD 格式返回。
• 该函数在使用时不需要参数。

2. NOW()

NOW() 是一个 MySQL 函数，用于返回当前的日期和时间，格式为 YYYY-MM-DD HH:MM:SS。它提供精确到秒的时间戳。

SELECT NOW();  //2024-09-20 14:23:15

注意事项

• NOW() 返回的时间戳是根据服务器的时区设置，如果需要特定时区的时间，可以使用 CONVERT_TZ() 函数进行转换。
• 该函数在使用时不需要参数。

3. DATE(date)

DATE() 是一个 MySQL 函数，用于从一个日期时间值中提取日期部分，返回格式为 YYYY-MM-DD。

SELECT DATE(NOW());  // 2024-09-20

注意事项

• DATE() 函数返回的结果总是 YYYY-MM-DD 格式，不包括时间部分。
• 如果输入的值不是有效的日期时间格式，函数将返回 NULL。

4. DATEDIFF(date1, date2)

DATEDIFF() 是一个 MySQL 函数，用于计算两个日期之间的差异，返回结果为相差的天数。

• 语法：

DATEDIFF(date1, date2) 

用法示例

• 计算两个日期之间的天数差：

SELECT DATEDIFF('2024-09-20', '2024-09-15') AS days_difference;

输出示例：5（表示两个日期之间相差 5 天）

• 过滤特定日期范围：

可以使用 DATEDIFF() 来筛选某个时间范围内的数据：

SELECT * FROM your_table 
WHERE DATEDIFF(NOW(), created_at) > 30;

这将返回创建时间超过 30 天的记录。

注意事项

• 返回的差值是一个整数，正值表示 date1 晚于 date2，负值则表示 date1 较早于 date2。
• 输入的日期格式必须是有效的日期格式，通常为 YYYY-MM-DD。

5. DATE_ADD(date, INTERVAL expr unit)

DATE_ADD() 是一个 MySQL 函数，用于向指定的日期添加一个时间间隔。这个函数可以用于日期的加法运算。

• 语法

DATE_ADD(date, INTERVAL value unit)

参数：
date：要进行加法运算的日期。
value：要添加的时间量。
unit：时间单位，如 DAY、MONTH、YEAR 等。

用法示例

• 向日期添加天数：

SELECT DATE_ADD('2024-09-20', INTERVAL 10 DAY) AS new_date;

输出示例：2024-09-30（表示在原日期基础上增加了 10 天）

• 向日期添加月份：

SELECT DATE_ADD('2024-09-20', INTERVAL 2 MONTH) AS new_date;

输出示例：2024-11-20（表示在原日期基础上增加了 2 个月）

• 向日期添加年份：

SELECT DATE_ADD('2024-09-20', INTERVAL 1 YEAR) AS new_date;

输出示例：2025-09-20（表示在原日期基础上增加了 1 年）

• 在查询中使用：

假设有一个包含创建日期的表，可以计算未来某个日期：

SELECT *, DATE_ADD(created_at, INTERVAL 30 DAY) AS future_date 
FROM your_table;

注意事项

• 时间单位可以是 SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR 等。
• 确保 value 是一个合适的数据类型（通常是整数）。

6. DATE_SUB(date, INTERVAL expr unit)

DATE_SUB() 是一个 MySQL 函数，用于从指定的日期中减去一个时间间隔

语法

DATE_SUB(date, INTERVAL value unit)

参数：
date：要进行减法运算的日期。
value：要减去的时间量。
unit：时间单位，如 DAY、MONTH、YEAR 等。

用法示例

• 从日期中减去天数：

SELECT DATE_SUB('2024-09-20', INTERVAL 10 DAY) AS new_date;

输出示例：2024-09-10（表示在原日期基础上减去 10 天）

• 从日期中减去月份：

SELECT DATE_SUB('2024-09-20', INTERVAL 2 MONTH) AS new_date;

输出示例：2024-07-20（表示在原日期基础上减去 2 个月）

• 从日期中减去年份：

SELECT DATE_SUB('2024-09-20', INTERVAL 1 YEAR) AS new_date;

输出示例：2023-09-20（表示在原日期基础上减去 1 年）

• 在查询中使用：

假设有一个包含创建日期的表，可以计算过去的日期：

SELECT *, DATE_SUB(created_at, INTERVAL 30 DAY) AS past_date 
FROM your_table;

注意事项

• 时间单位可以是 SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR 等。
• 确保 value 是一个合适的数据类型（通常是整数）。

7. YEAR(date)

返回日期的年份部分。

SELECT YEAR(NOW());  // 2024

8. MONTH(date)

返回日期的月份部分（1-12）。

SELECT MONTH(NOW());  // 9

9. DAY(date)

返回日期的天数部分（1-31）。

SELECT DAY(NOW());  // 20

10. WEEK(date)

返回日期是一年中的第几周。

SELECT WEEK(NOW());  // 38

11. LAST_DAY(date)

返回指定月份的最后一天。

SELECT LAST_DAY(NOW());  // 2024-09-30

12 TO_DAYS

TO_DAYS 函数在 MySQL 中用于将日期转换为天数。它可以用于计算两个日期之间的差异

SELECT *
FROM 表名
WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) = 1;

13 DATE_FORMAT

DATE_FORMAT 是 MySQL 中用于格式化日期和时间的函数。它允许你将日期转换为指定的格式

SELECT DATE_FORMAT(时间字段名, '%Y-%m-%d') AS 格式化日期
FROM 表名;

常用格式符
%Y: 四位年份（例如：2024）
%y: 两位年份（例如：24）
%m: 两位月份（01到12）
%d: 两位日期（01到31）
%H: 两位小时（00到23）
%i: 两位分钟（00到59）
%s: 两位秒数（00到59）

14 YEARWEEK()

YEARWEEK()是 MySQL 中的一个函数，用于获取指定日期的年份和周数。这个函数通常用于按周进行分组和筛选数据。

1 语法

YEARWEEK(date, mode)

• date: 要检查的日期值。
• mode: （可选）指定一周的第一天，默认为 0，表示周日为一周的第一天。常用的模式包括：

0: 周日为第一天
1: 周一为第一天
2: 周二为第一天
…
6: 周六为第一天
3-7: 与 ISO 8601 标准相同
2 示例用法
假设有一个名为 orders 的表，包含一个 created_at 列，记录订单的创建时间。你可以使用 YEARWEEK() 函数来获取当前周的数据：

SELECT *
FROM orders
WHERE YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1);

3 代码说明

• 在上面的查询中，YEARWEEK(created_at, 1) 将返回 created_at 日期的年份和周数（以周一为一周的开始）。
• YEARWEEK(CURDATE(), 1) 返回当前日期的年份和周数。
• 通过比较这两个值，查询会筛选出所有在当前周内创建的订单。

二 案例

1 INTERVAL 1 DAY

INTERVAL 1 DAY 是 MySQL 中用来表示时间间隔的关键字，可以用于日期和时间的运算。它的作用是增加或减少特定的时间单位。

示例用法

• 减少一天：

SELECT CURDATE() - INTERVAL 1 DAY; 

这将返回昨天的日期。

• 增加一天：

SELECT CURDATE() + INTERVAL 1 DAY; 

这将返回明天的日期。

适用场景

• 用于计算特定日期的前后日期。
• 在查询中筛选特定时间段的数据，例如获取昨天、上个月的数据等。是 MySQL
  中用来表示时间间隔的关键字，可以用于日期和时间的运算。它的作用是增加或减少特定的时间单位。

2 获取昨天的数据

• 使用 DATE_SUB

SELECT * 
FROM your_table 
WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

1 CURDATE()：获取当前日期（不包含时间）。
2 INTERVAL 1 DAY：表示时间间隔为 1 天。
3 DATE(created_at)：将 created_at 列中的日期时间转换为日期格式，以便进行比较。

• 使用 TO_DAYS
  TO_DAYS 函数在 MySQL 中用于将日期转换为天数。它可以用于计算两个日期之间的差异

SELECT
	*
FROM
	t_goods_product
WHERE
	TO_DAYS(NOW()) - TO_DAYS(crTime) = 1;

3 获取今天的数据

• 使用date

SELECT *
FROM 表名
WHERE DATE(时间字段名) = CURDATE();

• 使用 DATE_FORMAT：

SELECT *
FROM 表名
WHERE DATE_FORMAT(时间字段名, '%Y-%m-%d') = CURDATE();

4 获取上个月数据

• 使用 DATE_FORMAT

SELECT *
FROM 表名
WHERE DATE_FORMAT(时间字段名, '%Y-%m') = DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m');

• 使用 DATE_SUB 结合 DAY：

SELECT *
FROM 表名
WHERE 时间字段名 >= DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) DAY + 1 MONTH)
AND 时间字段名 < DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) DAY);

• 使用 DATE_ADD 和 LAST_DAY：

SELECT *
FROM 表名
WHERE 时间字段名 >= DATE_ADD(LAST_DAY(CURDATE() - INTERVAL 2 MONTH), INTERVAL 1 DAY)
AND 时间字段名 <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH);

5 获取当前一周的数据

获取当前一周的数据，可以使用 WEEK 函数或日期函数结合 CURDATE() 来筛选出当前周的记录。以下是一些常见的查询示例。

1: 使用 WEEK() 函数

假设你有一个名为 orders 的表，包含一个 created_at 列，记录订单的创建时间。你可以用以下查询获取当前一周的数据：

SELECT *
FROM orders
WHERE WEEK(created_at, 1) = WEEK(CURDATE(), 1)
AND YEAR(created_at) = YEAR(CURDATE());

2: 使用日期范围
另一种方法是直接计算当前周的开始和结束日期。默认情况下，一周从周一开始到周日结束。你可以这样写：

SELECT *
FROM orders
WHERE created_at >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
AND created_at < CURDATE() + INTERVAL (7 - WEEKDAY(CURDATE())) DAY;

3: 使用 YEARWEEK()
使用 YEARWEEK() 函数也是一种有效的方法，它将年份和周数组合在一起：

SELECT *
FROM orders
WHERE YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1);