Mysql查询今天和昨天等

create_time为时间字段名
查询今天的数据

SELECT
  * 
FROM
  dept 
WHERE
  TO_DAYS( create_time ) = TO_DAYS( NOW() );

查询昨天的数据

SELECT
  * 
FROM
  dept 
WHERE
  TO_DAYS ( NOW( ) ) - TO_DAYS( create_time ) = 1;

查询近7天的数据

SELECT
  * 
FROM
  dept 
WHERE
  DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= DATE( create_time );

查询近30天的数据

SELECT
  * 
FROM
  dept 
WHERE
  DATE_SUB( CURDATE(), INTERVAL 30 DAY ) <= DATE( create_time );

查询本月的数据

SELECT
  * 
FROM
  dept 
WHERE
  DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE(), '%Y%m' );

SELECT
  * 
FROM
  dept 
WHERE
  DATE_FORMAT( create_time, ' %Y%m ' ) = DATE_FORMAT(
    CURDATE(),
  ' %Y%m ' 
  )

查询上个月的数据

SELECT
  * 
FROM
  dept 
WHERE
  PERIOD_DIFF(
  DATE_FORMAt( NOW(), '%Y%m' ),
  DATE_FORMAT( create_time, '%Y%m' )) = 1;

SELECT
  * 
FROM
  dept 
WHERE
  date_format( create_time, '%Y-%m' )= date_format(
  DATE_SUB( curdate(), INTERVAL 1 MONTH ),
  '%Y-%m')

查询本季度的数据

SELECT
  * 
FROM
  dept 
WHERE
  QUARTER ( create_time ) = QUARTER (
  NOW());

查询上个季度的数据

SELECT
  * 
FROM
  dept 
WHERE
  QUARTER ( create_time ) = QUARTER (
  DATE_SUB( NOW(), INTERVAL 1 QUARTER ));

查询本年数据

SELECT
  *
FROM
  dept
WHERE
  YEAR ( create_time ) = YEAR (
  NOW());

查询上年数据

SELECT
  * 
FROM
  dept 
WHERE
  YEAR ( create_time ) = YEAR (
  DATE_SUB( NOW(), INTERVAL 1 YEAR ));

查询本周数据(周日开始到周六)

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

查询上周数据

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

查询本月的数据

SELECT
  * 
FROM
  dept 
WHERE
  DATE_FORMAT( create_time, '%Y-%m' ) = DATE_FORMAT( NOW(), '%Y-%m' );

查询距现在6个月的数据

SELECT
  * 
FROM
  dept 
WHERE
  create_time BETWEEN DATE_SUB( NOW(), INTERVAL 6 MONTH ) 
  AND NOW();