高难度时间计算问题

[zhsu]

在这样一个表中：
ID BeginDateTime EndDateTime TimeValue
1 20051201100000 20051201110000 3600
2 20051201100000 20051201100050 50
3 20051201093000 20051201103000 3600
4 20051201094000 20051201120000 8400
.........

意义是这样的：
BeginDateTime和EndDateTime都是bigint类型字段表示日期和时间（精确到秒），TimeValue是BeginDateTime和EndDateTime之间的时长（秒）。

现需要根据这个表里面的记录得到指定时间段内的TimeValue
如：在20051201110000到2005120113000000内所有的TimeValue总和为3600

[zhsu]

UP

[zhsu]

有解决方法也可以说一下，欢迎交流。

[OracleRoob]

现需要根据这个表里面的记录得到指定时间段内的TimeValue
如：在20051201110000到2005120113000000内所有的TimeValue总和为3600


@value1=20051201110000
@value2=2005120113000000
--------------------------------------------------------

如果给定的第二个值@value2在一条记录的BeginDateTime和EndDateTime之间呢？怎么计算？

[-狙击手-]

select BeginDateTime,EndDateTime ,datediff(s,substring(BeginDateTime,1,4) +'-'+substring(BeginDateTime,5,2)+'-'+substring(BeginDateTime,7,2)+ ' '+substring(BeginDateTime,9,2)+':'+substring(BeginDateTime,11,2)+':'+substring(BeginDateTime,13,2),substring(EndDateTime ,1,4) +'-'+substring(EndDateTime ,5,2)+'-'+substring(EndDateTime ,7,2)+ ' '+substring(EndDateTime ,9,2)+':'+substring(EndDateTime ,11,2)+':'+substring(EndDateTime ,13,2)) from table

[子陌红尘]

declare @t table(ID int,BeginDateTime datetime,EndDateTime datetime,TimeValue int)
insert into @t select 1,'2005-12-01 10:00:00','2005-12-01 11:00:00',3600
insert into @t select 2,'2005-12-01 10:00:00','2005-12-01 10:00:50',50
insert into @t select 3,'2005-12-01 09:30:00','2005-12-01 10:30:00',3600
insert into @t select 4,'2005-12-01 09:40:00','2005-12-01 12:00:00',8400



declare @sdate datetime,@edate datetime
set @sdate='2005-12-01 11:00:00'
set @edate='2005-12-01 13:00:00'

select
sum(case
when BeginDateTime between @sdate and @edate
then case
when EndDateTime between @sdate and @edate
then TimeValue
else
datediff(ss,BeginDateTime,@edate)
end
else
case
when EndDateTime between @sdate and @edate
then datediff(ss,@sdate,EndDateTime)
else
0
end
end) as TimeValue
from
@t

/*
TimeValue
-----------
3600
*/

[zhsu]

to:wangtiecheng(cappuccino)
你说的情况是有可能的

[-狙击手-]

要是有这格式2005-12-01 10:00:00只要用datediff(s,'','')

[zhsu]

to： libin_ftsafe(子陌红尘)
BeginDateTime的类型是bigint的转换成datetime的时候会报溢出。

[子陌红尘]

之前的SQL代码有BUG，修正一下：
----------------------------------------------------------------------------------------------------------------------------------
declare @t table(ID int,BeginDateTime datetime,EndDateTime datetime,TimeValue int)
insert into @t select 1,'2005-12-01 10:00:00','2005-12-01 11:00:00',3600
insert into @t select 2,'2005-12-01 10:00:00','2005-12-01 10:00:50',50
insert into @t select 3,'2005-12-01 09:30:00','2005-12-01 10:30:00',3600
insert into @t select 4,'2005-12-01 09:40:00','2005-12-01 12:00:00',8400


declare @sdate datetime,@edate datetime
set @sdate='2005-12-01 11:00:00'
set @edate='2005-12-01 13:00:00'

select
sum(case
--BeginDateTime and EndDateTime被选取的时间段包括
when (BeginDateTime between @sdate and @edate) and (EndDateTime between @sdate and @edate)
then TimeValue
--交集A
when BeginDateTime between @sdate and @edate
then datediff(ss,BeginDateTime,@edate)
--交集B
when EndDateTime between @sdate and @edate
then datediff(ss,@sdate,EndDateTime)
--选取的时间段被eginDateTime and EndDateTime包括
when BeginDateTime<@sdate and EndDateTime>@edate
then datediff(ss,@sdate,@edate)
else
0
end) as TimeValue
from
@t

/*
TimeValue
-----------
3600
*/

[qtyeee]

路过!

[子陌红尘]

declare @t table(ID int,BeginDateTime bigint,EndDateTime bigint,TimeValue int)
insert into @t select 1,'20051201100000','20051201110000',3600
insert into @t select 2,'20051201100000','20051201100050',50
insert into @t select 3,'20051201093000','20051201103000',3600
insert into @t select 4,'20051201094000','20051201120000',8400


declare @sdate datetime,@edate datetime
set @sdate='2005-12-01 11:00:00'
set @edate='2005-12-01 13:00:00'

select
sum(case
--BeginDateTime and EndDateTime被选取的时间段包括
when (BeginDateTime between @sdate and @edate) and (EndDateTime between @sdate and @edate)
then TimeValue
--交集A
when BeginDateTime between @sdate and @edate
then datediff(ss,BeginDateTime,@edate)
--交集B
when EndDateTime between @sdate and @edate
then datediff(ss,@sdate,EndDateTime)
--选取的时间段被eginDateTime and EndDateTime包括
when BeginDateTime<@sdate and EndDateTime>@edate
then datediff(ss,@sdate,@edate)
else
0
end) as TimeValue
from
(select
ID,
BeginDateTime=cast(left(rtrim(BeginDateTime),4)+'-'+substring(rtrim(BeginDateTime),5,2)+'-'+substring(rtrim(BeginDateTime),7,2)+' '+substring(rtrim(BeginDateTime),9,2)+':'+substring(rtrim(BeginDateTime),11,2)+':'+substring(rtrim(BeginDateTime),13,2) as datetime),
EndDateTime=cast(left(rtrim(EndDateTime),4)+'-'+substring(rtrim(EndDateTime),5,2)+'-'+substring(rtrim(EndDateTime),7,2)+' '+substring(rtrim(EndDateTime),9,2)+':'+substring(rtrim(EndDateTime),11,2)+':'+substring(rtrim(EndDateTime),13,2) as datetime),
TimeValue
from @t) a


/*
TimeValue
-----------
3600
*/

[zhsu]

看来不可避免的要将bigint类型的数据转换成datetime了

[ReViSion]

学习下

[server2000]

高深莫测.我是sql存储的菜鸟

[变成熊猫你就老实了是不]

试试 select sum(TimeValue) from tablename where BeginDateTime>20051201110000 and EndDateTime < 2005120113000000