• 查询某一天的数据
1
2
3

# create_time的格式是"2022-03-25 13:50:02"

SELECT * FROM xxx WHERE to_days(create_time) = to_days('2021-10-28');
  • 查询5天内的数据
1

SELECT * FROM xxx WHERE record_at >= DATE_SUB(CURDATE(), INTERVAL 5 DAY);
  • 昨天的数据
1

SELECT * FROM xxx WHERE to_days(now()) -  to_days(create_time) = 1;
  • 前天的数据
1
2
3

SELECT * FROM xxx 
WHERE to_days(now()) - to_days(create_time) < 2 
AND to_days(now()) - to_days(create_time) > 1;
  • 日期字段提取
1
2
3
4
5

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1