在mysql中计算两次之间的时差 [英] Calculate time difference between two times in mysql

问题描述

大家好我需要你的帮助。


每当有两个连续的I（IN）和O（OUT）时，
i需要计算时差
$列status中的b $ b然后必须添加连续I（IN）和O（OUT）的所有时差



注意： - 我（IN）在公司签到时间，O（OUT）退出公司的时间

hi guys i need your help.

i need to calculate time difference whenever there are two consecutive I (IN) and O (OUT)
in the column "status" and then have to add all time differences of consecutive I (IN) and O (OUT)

NOTE:- where I (IN) is sign in time in the company and O (OUT) is sign out time from the company

emp_no	 attandence_date	       sign_time                status
-------------------------------------------------------------------------
248	2016-09-27 00:00:00.000	   1900-01-01 07:40:59.000	   I
248	2016-09-27 00:00:00.000	   1900-01-01 12:07:54.000	   I
248	2016-09-27 00:00:00.000	   1900-01-01 14:48:20.000	   O
248	2016-09-27 00:00:00.000	   1900-01-01 14:48:38.000	   I
248	2016-09-27 00:00:00.000	   1900-01-01 14:50:24.000	   O
248	2016-09-27 00:00:00.000	   1900-01-01 15:04:08.000	   I
248	2016-09-27 00:00:00.000	   1900-01-01 15:33:04.000	   O
--------------------------------------------------------------------------

我的尝试：



这是我创建的功能，但我正在寻找一些简单的方法来做到这一点

What I have tried:

this is the function i have created but i am searching some simple way to do this

ALTER function [dbo].[fn_consumed_hours_inComp]
(
	@emp_id varchar(10),@attDate varchar(12) ,@in1_r varchar(5),@Out1_r varchar(5), @flag bit
)
returns varchar(15)
as

begin

declare @hours int, @totalMinutes int, @minutes int, @counter int, @maxid int, @in1 datetime, @out1 datetime,@status_i char(1),@status_o char(1),
	@hrs int, @mins int, @result varchar(15),@hr char(3), @mn char(3)

if @flag='True' --calculate total hrs in company
begin
	declare @day_att table (loop_id int identity(1,1),in1 datetime, status char(1))
	insert into @day_att(in1,status) select  signtime,status  from EmpAtt where empno = @emp_id and atdate = @attDate order by signtime
	
	select @maxid = count(loop_id) from @day_att
	set @counter = 1
	set @totalMinutes=0

	while @counter <= @maxid
	begin
		select @status_i=status from @day_att where loop_id=@counter
		select @status_o=status from @day_att where loop_id=@counter+1

		if @status_i = 'I' and @status_o ='O'
			begin
				select @in1=in1 from @day_att where loop_id=@counter
				select @out1=in1 from @day_att where loop_id=@counter+1

				select @minutes=DATEDIFF(MINUTE,@in1,@out1)
				set @totalMinutes=@totalMinutes+@minutes
				set @counter = @counter + 2
			end
		else
			set @counter = @counter + 1
	end

		select @hrs = @totalMinutes / 60
		select @mins = @totalMinutes % 60
                select @result = cast(@hrs as varchar(10)) + ':' + cast(@mins as varchar(10))
		
	end

	return @result
end

推荐答案

我有看到人们使用APPLY或CTE（通用表格式）轻松实现这种结果。

它也是可能的能够使用临时表和光标等来执行此操作，但我使用APPLY或CTE应该更容易消除错误的机会和所有这些复杂性。



请检查以下链接是否他们可以简单介绍一下我所说的内容。

sql - 计算出勤时间差 - Stack Overflow [ ^ ]

sql server - 如何计算单列工作时间？ - 数据库管理员堆栈交换 [ ^ ]

将顺序时间记录转换为IN和OUT时间 [ ^ ]



希望，这些有帮助:)

如果我遗失请告诉我这里显而易见的事情。

I have seen people achieving this kind of result easily using APPLY or CTE (Common Table Expression).
It is also possible to do this using temporary table and cursor etc but I using APPLY or CTE should be far easier removing chances of mistakes and all those complexities.

Please check following links if they can give a brief idea of what I am referring to.
sql - Calculate time difference for attendance - Stack Overflow[^]
sql server - How can I calculate time spent at work in a single column? - Database Administrators Stack Exchange[^]
Converting sequential time records into IN and OUT times[^]

Hope, these helps :)
Please let me know if I am missing something obvious here.

declare @day_att table (UserID INT,signtime DATETIME,status CHAR(1))
declare @hrs table (_hours float)
insert into @day_att(UserID,signtime,status) select empno,signtime,status from EmpAtt where empno = 248 and atdate = '2016-09-27' order by signtime

insert into @hrs(_hours) select CAST(DATEDIFF(MINUTE,I.signtime,O.signtime)/60.0 AS float) [Hours]
FROM @day_att I OUTER APPLY (SELECT TOP 1 signtime,status FROM @day_att t WHERE t.UserID = I.UserID AND t.signtime > I.signtime ORDER BY t.signtime) O
WHERE I.status = 'I' AND O.status = 'O'

select sum(_hours) as totalHrs from @hrs

这篇关于在mysql中计算两次之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！