Sum of Hours and minutes MySQL in two fields

Navigation
1

I am working with a program in PHP with connection to database MySQL everything has gone well except in the section to get the total number of hours between two columns named Entry and Exit.

The program captures the hour of entry and exit of the employees, now what I want is that if in the hour of departure it takes more than 20 min to leave and it meets an extra hour without salary, I mean that its hour of entry was at 9pm and goes to 4pm suman 7Hrs but if it takes and records 4:20pm already counts as one hour more, that is, 8Hrs in total.

To obtain the sum of the two columns of Hora_entrada and Hora_salida I do the following: 

SELECT HOUR(SUM(TIMEDIFF(Hora_entrada, Hora_salida))) FROM control;

That gives me the total sum of hours worked, but I do not add the minutes.

In which part should I improve my query to add the minutes along with the hours of the 2 columns?

    
asked by Santiago Huh 17.07.2017 в 20:17
source

4 answers

1

I understand that what you are looking for is this: 

SELECT  SUM( 
        HOUR(TIMEDIFF(Hora_entrada, Hora_salida)) + 
        CASE WHEN MINUTE(TIMEDIFF(Hora_entrada, Hora_salida)) % 60 > 20 THEN 1 ELSE 0 END
    )
    FROM control;

That is the sum of:

  • Difference in hours between Hora_entrada , Hora_salida
  • If the rest of dividing the difference in minutes by 60 (1 hour) is greater than 20 by 1 hour
    • answered by 17.07.2017 / 21:05
    source
    3

    The best thing in these cases that we already want to do something special is to pass it to seconds. You can do it in the following way: 

    SELECT ceil((UNIX_TIMESTAMP(Hora_salida) - 
UNIX_TIMESTAMP(Hora_entrada))/3600) from control;

    You pass UNIX_TIMESTAMP seconds to both fields, subtract it and divide it by 3600 to give you a decimal number. Then you just have to round up with the ceil function.

        
    answered by 17.07.2017 в 20:50
    0

    You are adding more functions to get the elapsed time, I'll give you an example to guide you how you could implement it.

    I get the date and time of the execution time of the select and annex assuming the entry date, in now () and the default date that place you can attach the time of entry and exit. 

    SELECT TIME(TIMEDIFF(NOW(),'2017-07-17 08:30:00.00'));

    Result: 

    05:13:06

    Now if you want to get a value it is specific use TIME_FORMAT() . 

    SELECT TIME_FORMAT(TIME(TIMEDIFF(NOW(),'2017-07-17 08:30:00.00')),'%H:%i');

    Result: 

     05:16
        
    answered by 17.07.2017 в 20:43
    0

    you need the second part of the query: 

    MINUTE(SUM(TIMEDIFF(Hora_entrada, Hora_salida)))

    must be something like you can verify it: 

    SELECT CONCAT(
HOUR(SUM(TIMEDIFF(Hora_entrada, Hora_salida))), ':',
MINUTE(SUM(TIMEDIFF(Hora_entrada, Hora_salida)))) FROM control;
        
    answered by 17.07.2017 в 20:31
    Doubt in data structure Batteries enable or unhide a button in swift 3