我想将时间段从'1小时'更改为'10分钟'。
并将显示时间从"10"更改为"10:00"
declare @periodStart datetime
declare @periodEnd datetime
set @periodStart = CONVERT(varchar(10), GETDATE() - 1, 120)
set @periodEnd = CONVERT(varchar(10), GETDATE() , 120)
set @periodStart = dateadd(HH, datepart(HH,@periodStart), convert(varchar(12),@periodStart,112))
set @periodEnd = dateadd(HH, datepart(HH,@periodEnd), convert(varchar(12),@periodEnd,112))
;with dh
as
(
select top 144
DATEADD(HOUR,ROW_NUMBER() OVER (ORDER BY [Object_id])-1,convert(varchar(12),@periodStart,112)) as HoDstart,
DATEADD(HOUR,ROW_NUMBER() OVER (ORDER BY [Object_id]),convert(varchar(12),@periodStart,112)) as HoDend,
ROW_NUMBER() OVER (ORDER BY Object_id)-1 as DayHour
from sys.columns
)
select d.DayHour, count(f.Hostname) as 'Counter'
from dh d
left join FileBackup f
on f.StartTime < d.HoDend
and f.EndTime >= d.HoDstart
where d.HoDstart between @periodStart and @periodEnd
group by d.DayHour
order by d.DayHour
看看这篇文章。
http://social.technet.microsoft.com/wiki/contents/articles/17976.t-sql-group-by-time-interval.aspx
我认为小组按 1/6 小时 - 每六分之一小时将是每 10 分钟。
重写查询,我们应该从最后一个答案中看到:
SELECT DATEPART(minute, logtime)/6 AS [SixthHour],
COUNT(loginid) AS [Logins Completed]
FROM somelog
GROUP BY DATEPART(minute, logtime)/6
这应该有帮助 - http://sqlfiddle.com/#!3/f60f3/1
带有想要的标签等,基于 Leptonator 的分钟/6 示例
DECLARE @periodStart datetime
DECLARE @periodEnd datetime
SET @periodStart = dateadd(DAY, datediff(DAY,0, GETDATE()), -1)
SET @periodEnd = dateadd(DAY, datediff(DAY,0, GETDATE()), 0)
SELECT
convert(varchar(5),dateadd(MINUTE,number*10,'00:00:00'),8) AS label
, x.Logins_Completed
FROM master..spt_values AS t1
LEFT JOIN (
SELECT
DATEPART(MINUTE, logtime)/6 AS [SixthHour]
, COUNT(loginid) AS [Logins_Completed]
FROM somelog
WHERE ( logtime >= @periodStart AND logtime < @periodEnd )
GROUP BY DATEPART(MINUTE, logtime)/6
) AS x ON t1.number = x.SixthHour
WHERE t1.type = 'P'
AND t1.number between 0 and 143;
查看此 sqlfiddle
额外观察:
不知道为什么要设置两次日期参数:以下两种情况都会为两个参数生成与现有参数相同的值:
/* easier to read */
declare @periodStart datetime
declare @periodEnd datetime
set @periodStart = CONVERT(varchar(10), GETDATE() - 1, 120)
set @periodEnd = CONVERT(varchar(10), GETDATE() , 120)
select
@periodStart
, @periodEnd
;
/* faster */
declare @periodStart datetime
declare @periodEnd datetime
set @periodStart = dateadd(day, datediff(day,0, GETDATE()), -1)
set @periodEnd = dateadd(day, datediff(day,0, GETDATE()), 0)
select
@periodStart
, @periodEnd
;
此外,您可能需要重新考虑使用"介于"作为日期范围选择方法:
where ( d.HoDstart >= @periodStart and d.HoDstart < @periodEnd )
参见:BETWEEN 和魔鬼有什么共同点?