MySQL Query isn't working in all the test cases

Question

I have the following table structure that has day wise shifts.

At only C shift, time is shared by two days.

As you can see C Shift starts at 20:30 and ends the next's days 06:00.

The Table structure and data as follows

create table `machine_shifts` (
   `date` date ,
   `shift_start_time` time ,
   `shift_end_time` time ,
   `shift` varchar (60),
   `updated_on` timestamp 
);

insert into `machine_shifts` (`date`, `shift_start_time`, `shift_end_time`, `shift`, `updated_on`) values('2010-01-01','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-01','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-01','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-02','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-02','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-02','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-03','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-03','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-03','22:30:00','06:00:00','C','2020-01-29 15:37:26'),
('2010-01-04','06:00:00','14:30:00','A','2020-01-29 15:37:26'),
('2010-01-04','14:30:00','22:30:00','B','2020-01-29 15:37:26'),
('2010-01-04','22:30:00','06:00:00','C','2020-01-29 15:37:27');

The Data is represented as follows

Now with the following query I'm achieve the data between the two date ranges. i.e between '2010-01-02 00:00:00' and '2010-01-03 10:00:00'.

SELECT * FROM machine_shifts
WHERE 
CONCAT(`date`, ' ', shift_start_time)
    BETWEEN '2010-01-02 00:00:00' AND '2010-01-03 10:00:00'
OR 
CONCAT(`date`, ' ', shift_end_time) + INTERVAL (shift = 'C') DAY
    BETWEEN '2010-01-02 00:00:00' AND '2010-01-03 10:00:00'
ORDER BY `date`, shift_start_time ASC

With the above query, what I'm getting is

And this is the result set I expected very much.I could achieve this by posting another question earlier.

The above query works but it is working only when two dates are different but when ever I want to get the data between two different times within same date, I'm not getting expected data. Please find the following sample of date time ranges.
-> '2010-01-01 07:01:00' AND '2010-01-01 14:00:00' With this I should the following expected data, but I'm getting empty result set.

I want the functionality of the above working query intact but also I should be getting data within a single day.

Edit: Just found that even in the above query there is problem with this range '2010-01-02 00:00:00' AND '2010-01-03 14:30:00'

SELECT * FROM machine_shifts
WHERE 
CONCAT(`date`, ' ', shift_start_time)
BETWEEN '2010-01-02 00:00:00' AND '2010-01-03 14:30:00'
OR 
CONCAT(`date`, ' ', shift_end_time) + INTERVAL (shift = 'C') DAY
BETWEEN '2010-01-02 00:00:00' AND '2010-01-03 14:30:00'
ORDER BY `date`, shift_start_time ASC

Here I'm getting

Here I'm not expecting the last row at all.

How can I achieve it? Can you suggest me some better approach## Heading ##

Answer 1

Each machine shift is from some timestamp to another. In your data model, however, you only store the start timestamp - separated into date and time for no apparent reason - and an end time. This is not sufficient, as the DBMS cannot know which date the end time is supposed to be in. It is only in your head. You should have either an end datetime or a shift duration instead.

Best would be you changed your data model, so as to have start and end stored as datetimes or timestamps. If you cannot do that, let's create them on the fly in a WITH clause:

with shifts as
(
  select
    addtime(`date`, shift_start_time) as start_time,
    addtime(`date` + interval case when shift_end_time < shift_start_time then 1 else 0 end day,
           shift_end_time) as end_time,
    shift,
    updated on
  from machine_shifts
)
select *
from shifts
where start_time <= timestamp '2010-01-01 07:01:00' 
  and end_time >= timestamp '2010-01-01 14:00:00';

(On a side note: Be careful when adding a time to a date. MySQL doesn't do this properly and comes up with a wrong result. This is why I am ising ADDTIME instead of a mere + above.)

Answer 2

SELECT * FROM machine_shifts
WHERE CONCAT(`date`, ' ', shift_start_time) <= @end
AND
CONCAT(`date`, ' ', shift_end_time) + INTERVAL (shift = 'C') DAY >= @start
ORDER BY `date`, shift_start_time ASC;

fiddle

Two timeranges overlaps when both (the start of one range is less then the end of another range). Or "is not more than" for "overlapped or adjacent" variant as in my solution/fiddle.

Answer 3

You can use

TIMEDIFF

select TIMEDIFF(cast(CONCAT('2010-01-01 ','06:00:00') as datetime),
cast(CONCAT('2010-01-01 ','14:30:00') as datetime));