求两个时间的时间差，但要去除某一段时间，求SQL语句

mayudong1 2010-03-23 05:29:10
表中存了一个开始时间和一个结束时间，比如字段分别为BeginTime和EndTime
现在要求求两个时间的时间差，但是要去除0-6点的这段时间。
比如begintime=2010-3-22 23:00:00 endtime=2010-3-23 7:00:00,统计出来的时长应该为2个小时
再比如，begintime=2010-3-23 1:00:00 endtime=2010-3-23 7:00:00,统计出来的时长应该为1个小时
再比如，begintime=2010-3-22 23:00:00 endtime=2010-3-23 5:00:00,统计出来的时长应该为1个小时
再比如，begintime=2010-3-23 1:00:00 endtime=2010-3-23 5:00:00,统计出来的时长应该为0
再比如，begintime=2010-3-21 23:00:00 endtime=2010-3-23 7:00:00,统计出来的时长应该为20个小时（这个不知道我有没有算错）

反正就是0-6点的时间段就都不算

求SQL语句
...全文
596 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
mayudong1 2010-03-24
  • 打赏
  • 举报
 回复
这么复杂，看崩溃了。

我的需求是这样的：
每条记录中有很多个字段，其中有开始时间和结束时间，做统计时需要统计时查出来的应该是除了原有的数据外，还要加上一个实际时间和之上说的那个去除0-6点的时间。
mayudong1 2010-03-24
  • 打赏
  • 举报
 回复
谢谢楼上各位了，如果我需要的是以分为单位的时间呢？
东那个升 2010-03-23
  • 打赏
  • 举报
 回复 
create table #tb

(

begintime    datetime,

endtime        datetime

)



insert into #tb

select '2010-3-22 23:00:00','2010-3-23 7:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 7:00:00' union all

select '2010-3-22 23:00:00','2010-3-23 5:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 5:00:00' union all

select '2010-3-21 23:00:00','2010-3-23 7:00:00'

select * from #tb



select	case when day=0 then case	when dtend<6 then 0 

									when dtbegin<6 and dtend>6 then dtend-6

									when dtbegin>6 and dtend>6 then dtend-dtbegin end  

			 when day=1 then case	when dtend<6 then 24-dtbegin

									when dtbegin<6 and dtend>6 then 18+dtend-6

									when dtbegin>6 and dtend>6 then dtend-6+(24-dtbegin) end

			 else case when dtend<6 and  dtend<6 then (24-6)*day

					   when dtend<6 and  dtend>6 then (24-6)*day+(dtend-6)

					   when dtend>6 and  dtend>6 then (24-6)*(day-1)+(dtend-6)+(24-dtbegin) end

		end as hour



from

(

select *,

datediff(day,begintime,endtime) day,

datepart(hh,begintime) dtbegin,

datepart(hh,endtime) dtend

from #tb

) a





       hour

-----------

          2

          1

          1

          0

         20



(5 行受影响)
dawugui 2010-03-23
  • 打赏
  • 举报
 回复
貌似正确了.以下是楼主提供的数据.代码和12楼一样.
create table tb(begintime datetime,endtime datetime)

insert into tb

select '2010-3-22 23:00:00','2010-3-23 7:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 7:00:00' union all

select '2010-3-22 23:00:00','2010-3-23 5:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 5:00:00' union all

select '2010-3-21 23:00:00','2010-3-23 7:00:00' 

go



select begintime , endtime,时间差=

       case when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6)

            when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime) - 1) * (24 - 6)

            when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6) + (datepart(hh,endtime) - 6)

            when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime) -1) * (24 - 6) + (datepart(hh,endtime) - 6)

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6) + (24 - datepart(hh,begintime))

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime)-1) * (24 - 6) + (24 - datepart(hh,begintime))

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6) + datepart(hh,endtime) - datepart(hh,begintime)

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime) - 1) * (24 - 6) + (datepart(hh,endtime) - 6) + (24 - datepart(hh,begintime))

       end

from tb



drop table tb



/*

begintime                                              endtime                                                时间差         

------------------------------------------------------ ------------------------------------------------------ ----------- 

2010-03-22 23:00:00.000                                2010-03-23 07:00:00.000                                2

2010-03-23 01:00:00.000                                2010-03-23 07:00:00.000                                1

2010-03-22 23:00:00.000                                2010-03-23 05:00:00.000                                1

2010-03-23 01:00:00.000                                2010-03-23 05:00:00.000                                0

2010-03-21 23:00:00.000                                2010-03-23 07:00:00.000                                20



（所影响的行数为 5 行）



*/
dawugui 2010-03-23
  • 打赏
  • 举报
 回复 
create table tb(begintime datetime,endtime datetime)

insert into tb

select '2010-3-22 17:00:00','2010-3-23 7:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 7:00:00' union all

select '2010-3-22 23:00:00','2010-3-23 5:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 5:00:00' union all

select '2010-3-21 23:00:00','2010-3-23 7:00:00' union all

select '2010-3-22 1:00:00','2010-3-22 7:00:00'

go



select begintime , endtime,时间差=

       case when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6)

            when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime) - 1) * (24 - 6)

            when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6) + (datepart(hh,endtime) - 6)

            when datepart(hh,begintime) between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime) -1) * (24 - 6) + (datepart(hh,endtime) - 6)

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6) + (24 - datepart(hh,begintime))

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime)-1) * (24 - 6) + (24 - datepart(hh,begintime))

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) <= datepart(hh,endtime) then datediff(day,begintime,endtime) * (24 - 6) + datepart(hh,endtime) - datepart(hh,begintime)

            when datepart(hh,begintime) not between 0 and 6 and datepart(hh,endtime) not between 0 and 6 and datepart(hh,begintime) > datepart(hh,endtime) then (datediff(day,begintime,endtime) - 1) * (24 - 6) + (datepart(hh,endtime) - 6) + (24 - datepart(hh,begintime))

       end

from tb



drop table tb



/*

begintime                                              endtime                                                时间差         

------------------------------------------------------ ------------------------------------------------------ ----------- 

2010-03-22 17:00:00.000                                2010-03-23 07:00:00.000                                8

2010-03-23 01:00:00.000                                2010-03-23 07:00:00.000                                1

2010-03-22 23:00:00.000                                2010-03-23 05:00:00.000                                1

2010-03-23 01:00:00.000                                2010-03-23 05:00:00.000                                0

2010-03-21 23:00:00.000                                2010-03-23 07:00:00.000                                20

2010-03-22 01:00:00.000                                2010-03-22 07:00:00.000                                1



（所影响的行数为 6 行）



*/
昵称被占用了 2010-03-23
  • 打赏
  • 举报
 回复
好像有错
有空再看
昵称被占用了 2010-03-23
  • 打赏
  • 举报
 回复
看看算得对不对先



select case when datediff(hour,Starttime,Endtime)<0  then 0

     when datediff(hour,Starttime,Endtime)>=0 and datediff(hour,Starttime,Endtime)<18  then datediff(hour,Starttime,Endtime)

     else datediff(hour,Starttime,Endtime) - (datediff(hour,Starttime,Endtime) + 6)/24*6

     end as result

      ,Starttime1 as Starttime

      ,Endtime1 as Endtime

from (     

select case when datepart(hour,Starttime) >= 0 and datepart(hour,Starttime) <= 6 then dateadd(hour,6-datepart(hour,Starttime),Starttime)

      else Starttime end as Starttime

      ,case when datepart(hour,Endtime) >= 0 and datepart(hour,Endtime) <= 6 then dateadd(hour,-datepart(hour,Endtime),Endtime)

      else Endtime end as Endtime

      ,Starttime as Starttime1

      ,Endtime as Endtime1

from (      

select '2010-01-01 2:00' as Starttime,'2010-01-01 4:00' as Endtime

union all

select '2010-01-01 2:00' as Starttime,'2010-01-01 7:00' as Endtime

union all

select '2010-01-01 2:00' as Starttime,'2010-01-02 4:00' as Endtime

union all

select '2010-01-01 2:00' as Starttime,'2010-01-01 23:00' as Endtime

union all

select '2010-01-01 8:00' as Starttime,'2010-01-01 23:00' as Endtime

union all

select '2010-01-01 8:00' as Starttime,'2010-01-02 5:00' as Endtime

union all

select '2010-01-01 8:00' as Starttime,'2010-01-02 7:00' as Endtime

union all

select '2010-01-01 8:00' as Starttime,'2010-01-02 23:00' as Endtime

) as t

) as t1



result      Starttime       Endtime

----------- --------------- ----------------

0           2010-01-01 2:00 2010-01-01 4:00

1           2010-01-01 2:00 2010-01-01 7:00

12          2010-01-01 2:00 2010-01-02 4:00

17          2010-01-01 2:00 2010-01-01 23:00

15          2010-01-01 8:00 2010-01-01 23:00

16          2010-01-01 8:00 2010-01-02 5:00

17          2010-01-01 8:00 2010-01-02 7:00

33          2010-01-01 8:00 2010-01-02 23:00



(8 行受影响)
东那个升 2010-03-23
  • 打赏
  • 举报
 回复
来过SP或者函数，写了个SP




create  proc t_sp @begintime datetime,@endtime datetime,@t int output

as

--set @begintime='2010-3-22 23:00:00'

--set @endtime='2010-3-23 7:00:00'





if datediff(day,@begintime,@endtime)>1

	BEGIN

	select @t=18*(datediff(day,@begintime,@endtime)-1)

	select 	@t=@t+case when  @begintime between convert(char(10),@begintime,120) and convert(char(10),@begintime,120)+' 6:00:00' then 18

				else datediff(hour,@begintime,dateadd(day,1,convert(char(10),@begintime,120))) end

			+

			case when  @endtime between convert(char(10),@endtime,120) and convert(char(10),@endtime,120)+' 6:00:00' then 0

				else datediff(hour,convert(char(10),@endtime,120)+' 6:00:00',@endtime) end

	END

if datediff(day,@begintime,@endtime)=1

	BEGIN

	select	@t=case when  @begintime between convert(char(10),@begintime,120) and convert(char(10),@begintime,120)+' 6:00:00' then 18

				else datediff(hour,@begintime,dateadd(day,1,convert(char(10),@begintime,120))) end

			+

			case when  @endtime between convert(char(10),@endtime,120) and convert(char(10),@endtime,120)+' 6:00:00' then 0

				else datediff(hour,convert(char(10),@endtime,120)+' 6:00:00',@endtime) end



	END

if datediff(day,@begintime,@endtime)=0



	BEGIN

	select	@t=case when	@endtime between convert(char(10),@endtime,120) and convert(char(10),@endtime,120)+' 6:00:00'  then 0

					else datediff(hour,case when	@begintime between convert(char(10),@begintime,120) and convert(char(10),@begintime,120)+' 6:00:00'  then convert(char(10),@begintime,120)+' 6:00:00' else @begintime end,@endtime) end

	END



RETURN @t







declare @i int

exec t_sp'2010-3-22 23:00:00','2010-3-23 7:00:00',@i output

select @i



exec t_sp'2010-3-23 1:00:00','2010-3-23 7:00:00',@i output

select @i



exec t_sp'2010-3-22 23:00:00','2010-3-23 5:00:00',@i output

select @i



exec t_sp'2010-3-23 1:00:00','2010-3-23 5:00:00',@i output

select @i



exec t_sp'2010-3-21 23:00:0','2010-3-23 7:00:00',@i output

select @i





           

-----------

          2



(1 行受影响)



           

-----------

          1



(1 行受影响)



           

-----------

          1



(1 行受影响)



           

-----------

          0



(1 行受影响)



           

-----------

         20



(1 行受影响)
jstoic 2010-03-23
  • 打赏
  • 举报
 回复
不好意思，错啦，下班回家再来分析
jstoic 2010-03-23
  • 打赏
  • 举报
 回复
试一下，没有仔细分析


create table #tb

(

begintime	datetime,

endtime		datetime

)



insert into #tb

select '2010-3-22 17:00:00','2010-3-23 7:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 7:00:00' union all

select '2010-3-22 23:00:00','2010-3-23 5:00:00' union all

select '2010-3-23 1:00:00','2010-3-23 5:00:00' union all

select '2010-3-21 23:00:00','2010-3-23 7:00:00' union all

select '2010-3-22 1:00:00','2010-3-22 7:00:00'

select * from #tb



select

case

when diff = 1 then 24 - dtbegin + dtend - 6

when diff > 1 then 24 - dtbegin + dtend - 6 + (diff-1)*(24-6)

when dtbegin <= 6 and dtend <= 6 then 0

when dtbegin < 6 and dtend > 6 then dtend - 6

when dtbegin > 6 then dtend - dtbegin

end hour

from

(

select 

datediff(day,begintime,endtime) diff,

datepart(hh,begintime) dtbegin,

datepart(hh,endtime) dtend

from #tb

) a



hour

-----------

8

1

0

0

20

1



(6 行受影响)
东那个升 2010-03-23
  • 打赏
  • 举报
 回复
先占楼，下流
ws_hgo 2010-03-23
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 fredrickhu 的回复:]
引用 2 楼 ws_hgo 的回复:
SQL code
select datediff(hour,dateadd(hour,6,'2010-3-22 23:00:00'),'2010-3-23 7:00:00')


-----------
2

(1 行受影响)


还需要判断的 小哥
[/Quote]

I See
战术
--小F-- 2010-03-23
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 ws_hgo 的回复:]
SQL code
select datediff(hour,dateadd(hour,6,'2010-3-22 23:00:00'),'2010-3-23 7:00:00')


-----------
2

(1 行受影响)
[/Quote]

还需要判断的 小哥
--小F-- 2010-03-23
  • 打赏
  • 举报
 回复
datediff
分两段加

下班撤退 晚上回去看看解决没...
ws_hgo 2010-03-23
  • 打赏
  • 举报
 回复 
select datediff(hour,dateadd(hour,6,'2010-3-22 23:00:00'),'2010-3-23 7:00:00')





-----------

2



(1 行受影响)
ws_hgo 2010-03-23
  • 打赏
  • 举报
 回复
datediff
sql计算时间差并排除周末
SQL计算时间差并排除周末 CREATE FUNCTION DI_FN_GET_WorkDay (@begin DATETIME , @end DATETIME ) RETURNS int BEGIN DECLARE @i INT , @j INT SET @i = 0 SET @j = 0 IF @end > @begin BEGIN WHILE...
关于sql语句中，统计时间重叠和时间不重叠
关于sql统计时间重叠和时间不重叠的问题 我们在开发过程中总会遇到这样的情况，一行数据中，有id、组、开始时间、结束时间。但是开始时间和结束时间肯定会出现时间重叠问题，比如说下面这种情况。 我们先找出时间...
数据库实现计算工作日时间差--去除节假日及周末
由于统计需求，要求计算2个时间差，并且要求去除节假日和周末的时间。 说一下需求规则： 1、如果开始时间和结束时间在一个休假时间段内，时间差为0，比如10.2到10.4这种。 2、其他情况，只计算在休假时间外的。 ...
如何查找MySQL中查询慢的SQL语句
这可能是困然很多人的一个问题，MySQL通过慢查询日志定位那些执行效率较低的SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件，...
SQL常用语句总结
MS SQL里没有括号时，运算进行的次序将是先乘后除再模后加减 减号(-)也有两种用途：1.作为负号使用 2.从某一列中减去另一列 and or not  如果一个where子句中同时出现这三个操作符 最先评估not 然后是and ...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章