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

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

SELECT
	time,
	COUNT( * ) AS num 
FROM
	(
SELECT
	id,
	DATE_FORMAT(
	concat(
	date( table_time ),
	' ',
	HOUR ( table_time ),
	':',
	floor( MINUTE ( table_time ) / 5 ) * 5 + 5 
	),
	'%Y-%m-%d %H:%i' 
	) AS time 
FROM
	table
WHERE
	table_time BETWEEN (select date_sub(now(), interval 1 hour)) 
	AND now() 
	) a 
GROUP BY
	DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY
	time;