数据类型

mysql 常用的时间格式的数据类型有几种：date、datetime、time、timestamp

内置日期函数

日期时间查询函数

1、查询当前日期：CURDATE()

// 2022-06-27
SELECT CURDATE();
SELECT CURRENT_DATE();

2、查询当前时间：CURTIME()

// 10:18:50
SELECT CURTIME();
SELECT CURRENT_TIME();

3、查询当前日期和时间

3.1、CURRENT_TIMESTAMP()

// 2022-06-27 10:21:58
SELECT CURRENT_TIMESTAMP();

3.2、NOW()

// 2022-06-27 10:21:58
SELECT NOW();

3.3、SYSDATE()

// 2022-06-27 10:21:58
SELECT SYSDATE();

日期时间格式转换函数

1、从日期或日期时间表达式中提取部分

按指定的提取类型提取：EXTRACT(unit FROM date)

MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH

SELECT EXTRACT(HOUR FROM '2022-06-27 10:21:58');	// 10

1.1、提取日期：DATE()

// 2022-06-27
SELECT DATE('2022-06-27 10:21:58');
SELECT DATE('2022-06-27');

1.2、提取天数：DAY()

SELECT DAY('2022-06-27');	// 27
SELECT DAY('2022-06-27 10:21:58');	// 27

1.3、提取星期几：DAYNAME()

SELECT DAYNAME('2022-06-27');	// Monday
SELECT DAYNAME('2022-06-27 10:21:58');	// Monday

1.4、本月第几天：DAYOFMONTH()

SELECT DAYOFMONTH('2022-06-27');	// 27
SELECT DAYOFMONTH('2022-06-27 10:21:58');	// 27

1.5、本周第几天（1星期日，2星期一）：DAYOFWEEK()

SELECT DAYOFWEEK('2022-06-27');	// 2
SELECT DAYOFWEEK('2022-06-27 10:21:58');	// 2

1.6、本年第几天：DAYOFYEAR()

SELECT DAYOFYEAR('2022-06-27');	// 178
SELECT DAYOFYEAR('2022-06-27 10:21:58');	// 178

2、按给定格式输出日期：DATE_FORMAT(date,format)

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');	// 2022-06-27

3、计算给定日期距离 0000年1月1日 的天数：TO_DAYS(date)

SELECT TO_DAYS('0000-01-02');	// 2
SELECT TO_DAYS(NOW());	// 738698

日期时间计算函数

1、当天加上 n 天：ADDDATE(date,INTERVAL expr unit)、ADDDATE(expr,days)

SELECT CURDATE();	// 今天 2022-06-27
SELECT ADDDATE(CURDATE(),INTERVAL 1 DAY);	// 明天 2022-06-28
SELECT ADDDATE(CURDATE(),INTERVAL 1 MONTH);	// 一个月后 2022-07-27

2、时间日期减函数：SUBDATE()

SELECT CURDATE();	// 2022-06-27
SELECT SUBDATE(CURDATE(),2);	// 2022-06-25

3、时段函数：PERIOD_ADD(period, number)、PERIOD_DIFF(period1, period2)

SELECT PERIOD_ADD(202201,6);	// 202207
SELECT PERIOD_DIFF(202207,202201);	// 月份差值 6

4、返回是第几季度：QUARTER()，1-4 季度

// now() 2022-06-27
SELECT QUARTER(NOW());	// 2

查询示例（通过以上内置函数组合查询）

组合方式不仅仅只有一种，下面也不是列举了所有的实现组合

1、查询当天的数据

SELECT * FROM tab where DATE(create_time) = CURDATE();
SELECT * FROM tab where TO_DAYS(create_time) = TO_DAYS(NOW());

2、查询昨天的数据

SELECT * FROM tab where DATE(create_time) = SUBDATE(CURDATE(),1);

3、查询7天内的数据

SELECT * FROM tab where DATE(create_time) >= SUBDATE(CURDATE(),7);
SELECT * FROM tab where SUBDATE(CURDATE(),INTERVAL 7 DAY) <= DATE(create_time);

4、查询本月的数据

SELECT * FROM tab where DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

5、查询上月的数据

SELECT * FROM tab where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(create_time,'%Y%m')) = 1;

6、查询本季度的数据

SELECT * FROM tab where QUARTER(create_time) = QUARTER(now());

7、查询上季度的数据

SELECT * FROM tab where QUARTER(create_time) = QUARTER(DATE_SUB(now(), interval 1 QUARTER));

8、查询本年的数据

SELECT * FROM tab where YEAR(create_time) = YEAR(NOW());

9、查询上年的数据

SELECT * FROM tab where YEAR(create_time) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

10、查询本周的数据

SELECT * FROM tab where YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW());

11、查询上周的数据

SELECT * FROM tab where YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) - 1;

12、查询当前以前6个月以内的数据

SELECT * FROM tab where create_time BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) and NOW();