MySql asks for a time range of every day, every hour, every minute

It is common to ask for statistical data every day for a period of time, or statistics at every point. But MySQL itself is a function or table that has no direct access to the point-in-time list. Here are some of the methods that you use to get a point-in-time list using temporary variables and a table that already exists to compare multiple data, which needs to be determined according to the actual situation . We hope to help you.

Some of the key data used in SQL

#t_table表是系统上已存在的一个有比较多数据的表

Every day of one months

#2017年7月份的每一天SET @beginDate='2017-07-01';SET @maxDate ='2017-07-31';SELECTDATE (@tempDay),@tempDay:=Date_add (@tempDay, INTERVAL1  Day) fromt_table F #该表是系统上已存在的一个有比较多数据的表 Left JOIN(SELECT @tempDay:=@beginDate) b on 1=1WHERE @tempDay<=@maxDate

Every hour of yesterday

#昨天的每一小时SET @beginDate=DATE (Date_add (now), INTERVAL-1  Day));SET @maxDate =DATE (now ());SELECTDate_format (@tempHour,'%H'),@tempHour:=Date_add (@tempHour, INTERVAL1HOUR) fromt_table F Left JOIN(SELECT @tempHour:=@beginDate) b on 1=1WHERE @tempHour <@maxDate

Every minute in an hour.

#一小时内的每一分钟SET @beginDate='2017-08-01 17:00:00';SET @maxDate ='2017-08-01 18:00:00';SELECTDate_format (@tempMinute,'%i'),@tempMinute:=Date_add (@tempMinute, INTERVAL1MINUTE) fromt_table T Left JOIN(SELECT @tempMinute:=@beginDate) b on 1=1WHERE @tempMinute <@maxDate

MySql asks for a time range of every day, every hour, every minute