获取时间范围内的每小时 [英] Get every hour for a time range

问题描述

所以我想要的是生成特定时间范围内的所有小时数.

So what I am trying to is generate all the hours that are inside a specific time range.

因此，考虑到上午 11 点到下午 2:00 的范围，我会得到:

So given the range 11 AM to 2:00 PM, I would get:

 11:00 AM
 12:00 PM
 1:00 PM
 2:00 PM

我试图避免必须存储商店可能营业的每个特定小时，而只存储范围(我需要将小时数与其他时间进行比较)

I am trying to avoid having to store every specific hour a store might be open and just store the range (I need to compare the hours against other times)

谢谢

推荐答案

如果你有一个 数字表(如果没有，请单击链接创建一个)...

If you have a numbers table (click the link to create one if you don't)...

create table test(
    startTime time
,   endTime time
)

insert into test
select '11:00', '14:00'

select
    dateadd(hh, n.n, t.startTime) as times
from test t
  inner join Numbers n
    -- assuming your numbers start at 1 rather than 0
    on n.n-1 <= datediff(hh, t.startTime, t.endTime)

如果这是专门的，您可以创建一个只有 24 个值的小时表.

If this is specialized, you can create an hours table with just 24 values.

create table HoursInADay(
    [hours] time not null
,   constraint PK_HoursInADay primary key ([hours])
)

-- insert
insert into HoursInADay select '1:00'
insert into HoursInADay select '2:00'
insert into HoursInADay select '3:00'
insert into HoursInADay select '4:00'
insert into HoursInADay select '5:00'
insert into HoursInADay select '6:00'
insert into HoursInADay select '7:00'
...

select
    h.[hours]
from test t
  inner join HoursInADay h
    on h.[hours] between t.startTime and t.endTime

这篇关于获取时间范围内的每小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！