MYSQL获取今日,获取昨日

MYSQL获取今日

只有日期不带时间

SELECT CURDATE() AS date

时间戳格式

SELECT now() AS datetime

MYSQL获取昨日

使用DATEDIFF() 函数,DATEDIFF()返回两个日期之间的天数,下面的sql为查询昨天返回-1

SELECT DATEDIFF('2019-10-29','2019-10-30') AS date

获取昨天数据

SELECT count(id) AS num FROM table WHERE DATEDIFF('日期字段',CURDATE())=-1

使用TIMESTAMPDIFF()函数，有参数设置，可以精确到年(YEAR)、月(MONTH)、日(DAY)、小时(HOUR)，分钟(MINUTE)和秒(SECOND)

TIMESTAMPDIFF(YEAR,smallTime, largeTime)

查询两个时间相差大于一年的数据

select count(id) from table where TIMESTAMPDIFF( YEAR, smallTime, largeTime) > 1

查询两个时间相差大于一个月的数据

select count(id) from table where TIMESTAMPDIFF( MONTH, smallTime, largeTime) > 1

查询一个小时内,每五分钟的数据

SELECTtime,COUNT( * ) AS num 
FROM(
SELECTid,DATE_FORMAT(concat(date( table_time ),' ',HOUR ( table_time ),':',floor( MINUTE ( table_time ) / 5 ) * 5 + 5 ),'%Y-%m-%d %H:%i' ) AS time 
FROMtable
WHEREtable_time BETWEEN (select date_sub(now(), interval 1 hour)) AND now() ) a 
GROUP BYDATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BYtime;