一、sql语句查询每分、时、天、月、年的平均数据
/*查询年、月、日、分钟的间隔的平均值(整月整时整分钟)*/
select
year(datetime), MONTH(datetime), DAY(datetime),
DATEPART(hour, datetime), DATEPART(MINUTE, datetime),AVG(value)
from mytable where
datetime>'2018-12-01 00:00:00' and datetime<'2018-11-30 00:00:00'
group by year(datetime), MONTH(datetime),DAY(datetime),
DATEPART(hour,datetime),DATEPART(MINUTE,datetime)
order by year(datetime),MONTH(datetime),DAY(datetime),
DATEPART(hour, datetime),DATEPART(MINUTE, datetime)
二、比如:查询某一时间段内每次间隔5分钟或半个小时的数据
select datetime,value,tagid from mytable
where datetime between '2018-02-02 14:00' and '2018-04-02 14:00'
-- 在 2017.10.10.12:1:00 和 2018.10.11.14:5:00 之间
and datediff(MINUTE,cast('2018-02-02 14:51:00' as datetime),datetime) %30 = 0
and tagid in(4,3) order by tagid,datetime desc --间隔7分钟
说明:
对于sql server而言,有些特定函数,可以对于时间类型的数据,自动提取相关信息
例如:
2018-02-01 14:32:12
year(datetime) :2018
month(datetime) :02
day(datetime) :01
DATEPART(day, datetime) : 01
DATEPART(minute, datetime):32
DATEPART(hour, datetime) :14
以及:datediff(),cast()等函数的用法