确定假期的时差 [英] Determine Time Difference on a holiday

问题描述

美好的一天，



我想知道如何解决我的这种困境。我不确定时差是否是最好的方法。



目标：在假期获得实际假期工作时间。



情况：12月25日这里的PH是法定假日，假设我们的员工在12月24日下午8:00到12月25日凌晨4:30开始工作，我们希望得到确切的结果。员工在假期工作多长时间。假日=''2013年12月25日''



例如，请参阅下面的表格详细信息。

Good Day,

I was wondering how to solve this dilemma of mine. I am not sure if getting time difference would be the best approach.

Objective: Get actual holiday working hours during holidays.

Situation: Every 25th of December here in PH is a legal holiday and assuming our employee starts working on 24th of December at 08:00 PM to 25th of December 4:30 AM, we would like to get the exact number of hours how long an employee worked during the holiday. Holiday = ''December 25, 2013''

For instance please see table details below.

Emp_name         Time_in                 Time_Out

Joey             12/24/2013 20:00:00     12/25/2013 04:30:00

预计结果：4小时& 30分钟



谢谢。

Expected results: 4 hours & 30 minutes

Thanks.

推荐答案

我没有MySQL，但这里看起来很像就像在Microsoft SQL Server Transact-SQL中一样。此示例处理假期期间没有小时的情况，并返回00:00:00。

I don''t have MySQL but here is what it looks like in Microsoft SQL Server Transact-SQL. This example handles the situation where there are no hours during the holiday and returns 00:00:00 for that case.

-- Create temp table for demonstration
Declare  @tb table (emp_name varchar(30),time_in DateTime, time_out DateTime);
insert into @tb (emp_name,time_in,time_out) values( 'Joey','2013-12-24 20:00:00','2013-12-25 04:30:00');
insert into @tb (emp_name,time_in,time_out) values( 'Not Joey','2013-12-24 20:00:00','2013-12-24 22:30:00');
insert into @tb (emp_name,time_in,time_out) values( 'Still Not Joey','2013-12-26 20:00:00','2013-12-26 22:30:00');
Declare @Holiday DateTime;
Set @Holiday = '2013-12-25 00:00:00'
--
-- Execute the SELECT statement
--
select
 emp_name,CAST(time_in as smalldatetime) As TimeIn,CAST(time_out as smalldatetime) as TimeOut,
 DATEADD(
    second,
    DATEDIFF(second,time_in,time_out),
    TIMEFROMPARTS(0,0,0,0,0)) as Total_Hours,
 DATEADD(second,
   IIF( DATEDIFF(second,
        IIF(@Holiday > time_in,@Holiday,time_in),
        IIF(DATEADD(Hour,24,@Holiday)< time_out,DATEADD(Hour,24,@Holiday),time_out))<0,0,
    DATEDIFF(second,
        IIF(@Holiday > time_in,@Holiday,time_in),
        IIF(DATEADD(Hour,24,@Holiday)< time_out,DATEADD(Hour,24,@Holiday),time_out))),TIMEFROMPARTS(0,0,0,0,0)) as Holiday_Hours
 from @tb

emp_name        TimeIn              TimeOut             Total_Hours Holiday_Hours
Joey            2013-12-24 20:00:00 2013-12-25 04:30:00   08:30:00    04:30:00
Not Joey        2013-12-24 20:00:00 2013-12-24 22:30:00   02:30:00    00:00:00
Still Not Joey  2013-12-26 20:00:00 2013-12-26 22:30:00   02:30:00    00:00:00

尝试在MySQL版本（没有MySQL数据库软件的好处）：

Attempt at MySQL version (without benefit of MySQL database software):

select
 emp_name,time_in,time_out,
 DATE_ADD(TIMEFROMPARTS(0,0,0,0,0), INTERVAL TIME_TO_SEC(TIMEDIFF(time_out,time_in)) SECOND) as Total_Hours,
 DATE_ADD(TIMEFROMPARTS(0,0,0,0,0), INTERVAL
    IIF(
      TIME_TO_SEC(TIMEDIFF(
    IIF(
         DATE_ADD('2013-12-25 00:00:00',INTERVAL 24 HOUR) < time_out,
     DATE_ADD('2013-12-25 00:00:00',INTERVAL 24 HOUR),time_out
           ),
         IIF('2013-12-25 00:00:00' > time_in,'2013-12-25 00:00:00',time_in)
                )
    )<0,0,
    TIME_TO_SEC(
    TIMEDIFF(
        IIF(DATE_ADD('2013-12-25 00:00:00',INTERVAL 24 HOUR) < time_out,DATE_ADD('2013-12-25 00:00:00',INTERVAL 24 HOUR),time_out)
        ),
            IIF('2013-12-25 00:00:00' > time_in,'2013-12-25 00:00:00',time_in)
        )
    ) SECOND) as Holiday_Hours
 from @tb

请按照以下链接：

DATE_SUB function.php [ ^ ]

TIMEDIFF函数 [ ^ ]



步骤：

1）从TimeOut减去小时数（12/25/2013 00:00:00）

2）得到时间减去日期之间的差异（视频：1）和TimeOut



这就是全部！

Please, follow below links:
DATE_SUB function.php[^]
TIMEDIFF function[^]

Steps to do:
1) Substract hours from TimeOut to get zero time (12/25/2013 00:00:00)
2) get time diff between substracted date (vide: 1) and TimeOut

That''s all!

这篇关于确定假期的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！