2
\$\begingroup\$

I have a solution for a question I asked on SO:

I am trying to create a database where I also need to find the time difference inside a record. However, timediff() does not give the result I need. What I mean is that there are 3 fixed time intervals in every day and each record stores time_1 & time_2 datetime entries.

My question is, for example, if time_1='2020-01-01 04:30' and time_2= '2020-01-08 15:45' of a single record is given, then how is it possible to find exact time difference in that record?

For simplicity, we can assume that fixedtime1 is the interval 5:00 and 6:00, fixedtime2 is the interval 13:00 and 14:00 and fixedtime3 is the interval 21:00 and 22:00. You can calculate it inside your head easily for the record obtained.

drop    table   if  exists  testtable;
create  table   TestTable(
id  int primary key,
time_1  datetime    not null,
time_2  datetime    not null,
textbox text);
insert  into    testtable(id,time_1,time_2)
values
(1,'2020-01-01  04:30','2020-01-08  15:45'),    /*exact =   9420    */  
(2,'2021-05-06  16:55','2021-07-01  23:15'),    /*exact =   70880   */  
(3,'2021-12-30  19:55','2022-01-02  03:10'),    /*exact =   2955 */ 
(4,'2021-12-31  23:59','2022-01-01  05:14'),    /*exact =   255  */ 
(5,'2022-01-01  01:01','2021-01-01  01:30'),    /*exact =   29  */  
(6,'2021-01-01  23:20','2021-01-02  00:10');


set @breakst1 = '03:45:00';
set @breakend1 =    '04:45:00';
set @breakst2 = '12:00:00';
set @breakend2 =    '13:00:00';
set @breakst3 = '18:00:00';
set @breakend3 =    '19:00:00';

select 

(case   when    time(time_1) between @breakst1 and @breakend1                                                   /*time_to_sec(timediff(time_1,concat(date(time_1)," ",@breakst1)))/60   between 0 and 60    then    0   old*/
                then    0                                       
                when    time(time_1) between @breakend1 and @breakst2                                                   /*time_to_sec(timediff(time_1,concat(date(time_1)," ",@breakst1)))/60   between 60 and 495      old*/
                THEN    
                        -abs(time_to_sec(
                                        timediff(time_1,concat(date(time_1),"   ",@breakst1))
                                                                                            )/60-60)
                WHEN    time(time_1) between @breakst2 and @breakend2                                                   /*brand new*/
                THEN    
                        -abs(time_to_sec(
                                        timediff(time_1,concat(date(time_1),"   ",@breakst1))
                                                                                            )/60-60)+
                        time_to_sec(timediff(time(time_1),@breakst2))/60                                                /*brand new*/
                WHEN    time(time_1) between @breakend2 and @breakst3           /*new*/                                 /*time_to_sec(timediff(time_1,concat(date(time_1)," ",@breakst1)))/60   between 495 and 855     old*/
                THEN    
                        -abs(time_to_sec(
                                        timediff(time_1,concat(date(time_1),"   ",@breakst1))
                                                                                            )/60-120)
                WHEN    time(time_1) between @breakst3 and @breakend3                                                   /*brand new*/
                THEN    
                        -abs(time_to_sec(
                                        timediff(time_1,concat(date(time_1),"   ",@breakst1))
                                                                                            )/60-120)+
                        time_to_sec(timediff(time(time_1),@breakst3))/60                                                /*brand new*/
                WHEN    time(time_1) between @breakend3 and '23:59:59'          /*new*/                                 /*time_to_sec(timediff(time_1,concat(date(time_1)," ",@breakst1)))/60   between 855 AND 1214    old*/
                THEN    
                        -abs(time_to_sec(
                                        timediff(time_1,concat(date(time_1),"   ",@breakst1))
                                                                                            )/60-180)
                
                else    
                        abs(time_to_sec(timediff(time_1,concat(date(time_1),"   ",@breakst1)))/60)  
        end)    
        
       as pt1,  /*you can replace this part without "as pt1,"  and you will get sum*/
                
        (case   when    time(time_2) between '00:00:00' and @breakst1           /*new*/                                 /*(time_to_sec(timediff(concat(date(time_1),"   ",time(time_2)),concat(date(time_1),"   ",@breakend1)))/60) between -285 and -60    old*/
                then    
                        -abs(time_to_sec(
                                        timediff(concat(date(time_1),"  ",time(time_2)),
                                                concat(date(time_1),"   ",@breakend1))
                                        )/60+60)
                when    time(time_2) between @breakst1 and @breakend1           /*new*/                                 /*(time_to_sec(timediff(concat(date(time_1),"   ",time(time_2)),concat(date(time_1),"   ",@breakend1)))/60) between -60 and 0   then    0   old*/
                then    0
                when    time(time_2) between @breakend1 and @breakst2           /*new*/                                 /*(time_to_sec(timediff(concat(date(time_1),"   ",time(time_2)),concat(date(time_1),"   ",@breakend1)))/60) between 0 and   435 old*/
                then    
                        abs(time_to_sec(
                                        timediff(concat(date(time_1),"  ",time(time_2)),
                                                concat(date(time_1),"   ",@breakend1))
                                        )/60)
                when    time(time_2) between @breakst2 and @breakend2                                                   /*(time_to_sec(timediff(concat(date(time_1),"   ",time(time_2)),concat(date(time_1),"   ",@breakend1)))/60) between 435 and 495 then    435 old*/
                THEN    
                        abs(time_to_sec(
                                        timediff(concat(date(time_1),"  ",time(time_2)),
                                                concat(date(time_1),"   ",@breakend1))
                                        )/60)-
                        time_to_sec(timediff(time(time_2),@breakst2))/60                                                /*BRAND NEW*/
                when    time(time_2) between @breakend2 and @breakst3           /*new*/                                 /*(time_to_sec(timediff(concat(date(time_1),"   ",time(time_2)),concat(date(time_1),"   ",@breakend1)))/60) between 495 and 795 OLD*/
                then    
                        abs(time_to_sec(
                                        timediff(concat(date(time_1),"  ",time(time_2)),
                                                concat(date(time_1),"   ",@breakend1))
                                        )/60-60)
                when    time(time_2) between @breakst3 and @breakend3           /*new*/                                 /*(time_to_sec(timediff(concat(date(time_1),"   ",time(time_2)),concat(date(time_1),"   ",@breakend1)))/60) between 795 and 855 then    735 OLD*/
                THEN    
                        (abs(time_to_sec(
                                        timediff(concat(date(time_1),"  ",time(time_2)),
                                                concat(date(time_1),"   ",@breakend1))
                                        )/60-60)-
                        time_to_sec(timediff(time(time_2),@breakst3))/60)                                               /*brand new*/
                when    time(time_2) between @breakend3 and '23:59:59'      /*new*/                                     /*(time_to_sec(timediff(concat(date(time_1),"   ",time(time_2)),concat(date(time_1),"   ",@breakend1)))/60) between 855 and 1154    OLD*/
                then    
                        abs(time_to_sec(
                                        timediff(concat(date(time_1),"  ",time(time_2)),
                                                concat(date(time_1),"   ",@breakend1))
                                        )/60-120)
                else    
                        abs(time_to_sec(
                                        timediff(concat(date(time_1),"  ",time(time_2)),
                                        concat(date(time_1),"   ",@breakend1))
                                        )/60-60)    
        end)    as pt2, /*you can replace this part without "as pt2,"  and you will get sum*/
                
       (case when time(time_2)>time(time_1) 
                then
                    case    when    time_to_sec(timediff(time_2,time_1))/60<1260    then    0
                    else    datediff(time_2,time_1)*1260    
                    end
            when time(time_1)>=time(time_2) then    datediff(time_2,time_1)*1260
        else 'error_final_step'
        end) as pt3
from testtable

I revised the code, and now it works as intended without any mistakes so far. Now it calculates 3 things, and the result is the sum of pt1, pt2 and pt3.

\$\endgroup\$
6
  • 1
    \$\begingroup\$ Note that some RDBMS will spit this out, because # is not a comment in SQL; -- is. \$\endgroup\$
    – Reinderien
    Commented Jan 25, 2022 at 14:45
  • \$\begingroup\$ This solution looks terribly complex. With a few corrections I am able run your code, but I am absolutely not sure the results are not correct. Are you working with minutes? Why?! There's no explanation of the code at all. I would also put the breaks in a database table. \$\endgroup\$
    – KIKO Software
    Commented Jan 25, 2022 at 15:08
  • \$\begingroup\$ Yeah i know and it references the the only 1 breakvalue rather than using it all. And my question explains the problem and struggles i have been through to calculate it. My real problem is convert this piece of complexity into simplier and more understandable code. I am using MySQL WB InnoDB latest version as of now. And # or /**/ comments my notes. If i get to find time, i will rewrite the whole thing. I am quitte busy with reporting stuff since new year and other sorts of things.idea came up to find a better solution to tracking stop start times and time loss calculations as a side project. \$\endgroup\$
    – Memo Haldan
    Commented Jan 25, 2022 at 17:27
  • \$\begingroup\$ Where does "495" come from? What is special about that number (and all the other constants other then "60"? \$\endgroup\$
    – Rick James
    Commented Feb 14, 2022 at 17:41
  • \$\begingroup\$ 495 is the time difference between end of the break and start of another in minutes. I actually rewrote the entire code but since i am not using my own laptop for a while i wasnt able to upload newer one. If i get a chance, will do it \$\endgroup\$
    – Memo Haldan
    Commented Feb 16, 2022 at 5:06

1 Answer 1

Reset to default
1
\$\begingroup\$

The code is too complicated, difficult to maintain and error-prone.

Here is a cleaner solution:

  1. Create a table of time periods, with break flag:

    Start End Break
    00:00 03:45 false
    03:45 04:45 true
    04:45 12:00 false
    12:00 13:00 true
    13:00 18:00 false
    18:00 19:00 true
    19:00 24:00 false

  2. Join this table to your data on overlapping time intervals

  3. Calculate starts and ends of new subintervals, with GREATEST and LEAST

  4. Exclude the breaks and sum the lengths of remaining intervals

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.