本文共 1255 字,大约阅读时间需要 4 分钟。
示例内容说明:表名 = demo ;时间字段 = create_time
DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
select DATE_FORMAT(t.create_time, '%Y%m%d') daysfrom demo tgroup by days;
select DATE_FORMAT(t.create_time, '%Y%u') weeksfrom demo tgroup by weeks;
select DATE_FORMAT(t.create_time, '%Y%m') monthsfrom demo tgroup by months;
select DATE_FORMAT(t.create_time, '%Y') yearsfrom demo tgroup by years;
SELECT * FROM demo t WHERE TO_DAYS(t.create_time) = TO_DAYS(NOW());
SELECT * FROM demo t WHERE TO_DAYS(NOW()) - TO_DAYS(t.create_time) = 1;
SELECT * FROM demo t WHERE DATE_SUB(CURDATE(),INTERVAL 5 DAY) <= DATE(t.create_time)
SELECT * FROM demo t WHERE BETWEEN DATE_SUB(NOW(),INTERVAL 1 MONTH) and NOW();
SELECT * FROM demo t WHERE YEARWEEK(DATE_FORMAT(t.create_time,'%Y-%m-%d'), 1) = YEARWEEK(NOW(), 1);
SELECT * FROM demo t WHERE YEARWEEK(DATE_FORMAT(t.create_time,'%Y-%m-%d'), 1) = YEARWEEK(NOW(), 1) -1;
SELECT * FROM demo t WHERE DATE_FORMAT(t.create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m');
SELECT * FROM demo t WHERE DATE_FORMAT(t.create_time,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y-%m');
转载地址:http://qwnws.baihongyu.com/