SQL SQL时间查询从晚上6点到早上6点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15505686/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL time query from 6pm at night to 6am
提问by nadG
How to count number of records after 6 at night and 6 in the morning?
如何计算晚上6点和早上6点后的记录数？
This works until 12pm.
这工作到中午 12 点。
SELECT distinct count(barcode) c
FROM table1
where DAY(timestamp) = DAY(GETDATE())
AND MONTH(timestamp) = MONTH(GETDATE())
AND YEAR(timestamp) = YEAR(GETDATE())
AND datepart(hh,timestamp) >= 18
AND datepart(hh,timestamp) >= 6;
回答by sgeddes
I think this may be all that you're looking for. It removes any seconds from the GETDATE() call and adds the appropriate amount of hours to it.
我想这可能就是你要找的。它从 GETDATE() 调用中删除任何秒数，并向其添加适当的小时数。
SELECT COUNT(barcode)
FROM table1
WHERE timestamp >= DATEADD(HOUR,18,CONVERT(VARCHAR(10), GETDATE(),110))
AND timestamp <= DATEADD(HOUR,6,CONVERT(VARCHAR(10), GETDATE()+1,110))
回答by Gordon Linoff
If you are trying to count things by day, but to have the day start at 6 p.m. rather than midnight, just add an offset to the time:
如果您想按天计算事物，但要让一天从下午 6 点而不是午夜开始，只需向时间添加一个偏移量：
select cast(timestamp + 0.25 as date) as theday, count(barcode)
from table1
group by cast(timestamp + 0.25 as date)
order by theday desc;
If you wanted to do the count for 6p.m. - 6a.m. for multiple days:
如果你想计算下午 6 点。- 早上 6 点 多日：
select cast(timestamp + 0.25 as date) as theday, count(barcode)
from table1
where datepart(hh, timestamp) in (18, 19, 20, 21, 22, 23, 0, 1, 2, 3, 4, 5)
group by cast(timestamp + 0.25 as date)
order by theday desc;
For the most recent day, you could do:
对于最近一天，您可以执行以下操作：
select top 1 cast(timestamp + 0.25 as date) as theday, count(barcode)
from table1
where datepart(hh, timestamp) in (18, 19, 20, 21, 22, 23, 0, 1, 2, 3, 4, 5)
group by cast(timestamp + 0.25 as date)
order by theday desc;
回答by Dirk Nachbar
How about this?
这个怎么样？
where datefunction(timestamp)>=getdate()-1 and (datepart(hh,timestamp)>=18 or datepart(hh,timestamp)<=6)
其中 datefunction(timestamp)>=getdate()-1 和 (datepart(hh,timestamp)>=18 或 datepart(hh,timestamp)<=6)
where datefunction converts datetime to date
其中 datefunction 将日期时间转换为日期
