How to guarantee that rows in mysql show up in an specific way

Question

I'm trying to get the hours worked per day by id, the table has two columns (id, registro):

id     registro
1      2017-08-23 08:00:00  -starting working day
1      2017-08-23 12:00:00  -stop for lunching time
2      2017-08-23 12:40:00  
1      2017-08-23 13:00:00  - return from lunching time
1      2017-08-23 17:00:00  - going home

Then I copied the idea from : Get total hours worked in a day mysql in order to create a column in_out and got this:

set @num := 0;
select tab.id, date_format(`registro`,'%d/%m/%Y') as date_,
SUM(UNIX_TIMESTAMP(`registro`)*(1-2*`in_out`))/3600 AS `hours_worked`
 from (select id, registro, mod(@num := @num+1,2) as in_out from registro_ponto_v
    where id= 1
    and date_format(registro,"%d-%m-%Y") = '23-08-2017'
    order by registro asc) as tab 
group by tab.id, date_;

When I execute on (mysql 14.14 dist 5.7.16 (x86_64) it result 8hs correctly, but when I execute on server version 10.1 on debian 9 for some reason the in_out column doesn't show the same order of rows in subselect... (8hs - 1; 12hs - 0; 13hs -1; 17hs -0) and so the sum of hours get an value different. Then my point is: there's a way to garantee that the order of rows match to this:

id    registro  in_out
1     8hs        1
1     12hs       0
1     13hs       1
1     17hs       0

The "order by" in the sub query won't be respected by the outer query. Try moving it to the outer query. — Jaydee, Aug 24 '17 at 15:20
@Strawberry the first entry row would be "in" (1), second would be "out" (0) no matter the time elapsed. — nel, Aug 25 '17 at 12:33
@Jaydee I suppose that it's happening here, the order by in the sub query is been ignored — nel, Aug 25 '17 at 12:37

score 0 · Answer 1 · answered Aug 24 '17 at 15:32

I think this would be simpler if you were able to explicitly store 'ins' and 'outs'...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL
, registro DATETIME NOT NULL
,status ENUM('in','out') NOT NULL
,PRIMARY KEY(id,registro)
);

INSERT INTO my_table VALUES
(1,'2017-08-23 08:00:00','in'),
(1,'2017-08-23 12:00:00','out'),
(2,'2017-08-23 12:40:00','in'),
(1,'2017-08-23 13:00:00','in'),
(1,'2017-08-23 17:00:00','out');

SELECT id
     , DATE(user_in) date
     , SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(user_out,user_in)))) total 
  FROM
     ( SELECT x.id,x.registro user_in,MIN(y.registro) user_out 
         FROM my_table x 
         JOIN my_table y 
           ON y.id = x.id 
          AND y.registro > x.registro 
          AND y.status = 'out' 
        WHERE x.status = 'in' 
        GROUP 
           BY x.id, x.registro
     ) a
 GROUP 
    BY id,date;
+----+------------+----------+
| id | date       | total    |
+----+------------+----------+
|  1 | 2017-08-23 | 08:00:00 |
+----+------------+----------+

How to guarantee that rows in mysql show up in an specific way

1 Answers1