我正在尝试对观察表中的结果进行分组。我的表每隔几秒钟就包含一次股票价值。我希望能够显示有关股票的汇总信息每2小时,4小时8小时,等等,但我需要这些时间段从下午5点开始例如,如果数据从2012-05-23 17:45:34开始,则第一次观测将从23号17:00:00开始,直到23号19:00:00,依此类推。我可以根据时间间隔对数据进行分组,但我不知道如何将开始时间设置为下午5点。
发布于 2012-05-24 03:38:03
为什么不在where子句中添加一个条件。利用时间(从你的datetime中)并检查以确保它大于17:00:00。如果它是在下午5点之前,那么不要在计算中使用它。
编辑您提到的,您已经完成了分组。在where子句中添加子查询的最后一天下午5点开始如何(不确定语法):
where datetimeCol >= (select min(date(datetimeCol) + time(5pm)) from tableUsed where previousUsedConditions or id = outerTable.Id)编辑
我知道您有一个更复杂的分组,但(在tsql中)这就是我使用where语句时想到的。
declare @tempTable table (mydate smalldatetime)
insert into @tempTable
values('2012-05-23 04:37:04.900'),
('2012-05-23 18:37:04.900'),
('2012-05-24 04:37:04.900'),
('2012-05-25 05:37:04.900')
select *
from @tempTable as tbl
group by mydate
select (cast(cast((select min(temp.myDate) from @tempTable as temp) as DATE) as datetime) + cast(cast( '17:00:00.0000000' as time)as datetime)) as 'This is the time that is getting filtered by below'
select *
from @tempTable as tbl
where tbl.mydate > (cast(cast((select min(temp.myDate) from @tempTable as temp) as DATE) as datetime) + cast(cast( '17:00:00.0000000' as time)as datetime)) 这将产生以下结果:
(4 row(s) affected)
mydate
-----------------------
2012-05-23 04:37:00
2012-05-23 18:37:00
2012-05-24 04:37:00
2012-05-25 05:37:00
(4 row(s) affected)
This is the time that is getting filtered by below
--------------------------------------------------
2012-05-23 17:00:00.000
(1 row(s) affected)
mydate
-----------------------
2012-05-23 18:37:00
2012-05-24 04:37:00
2012-05-25 05:37:00
(3 row(s) affected)https://stackoverflow.com/questions/10726614
复制相似问题