CODEWITHSUNDEEP
sqlselecttimebetween
Zoey Malkov
Zoey Malkov

Reputation: 832

How to select within a time range and not within a time range

I have employees. It is currently 9:30 am. I want to select everyone who is working right now. Then in a separate sql statement, I want to select everyone who is not working right now. How can I do this? I am having trouble wrapping my head around the logic. I have used the time field rather than datetime on purpose.

My table with sample data

CREATE TABLE shifts (

    id                      int unsigned
                            AUTO_INCREMENT
                            PRIMARY KEY,

    name                    varchar(64)
                            NOT NULL,

    start_time              time
                            NOT NULL,

    end_time                time
                            NOT NULL
);

INSERT INTO shifts (id, name, start_time, end_time) VALUES
(1, 'Bob', '09:00:00', '17:00:00'),
(2, 'Jack', '09:30:00', '14:00:00'),
(3, 'Bill', '15:00:00', '03:00:00'),
(4, 'James', '23:30:00', '10:00:00'),
(5, 'Sarah', '14:30:00', '21:00:00'),
(6, 'Marry', '03:00:00', '09:30:00');

People who are working:

name
Bob
Jack
James

People who are not working:

name
Bill
Sarah
Marry

I cannot wrap my head around it, my attempt at people who are working is wrong but...:

SELECT name
FROM shifts
WHERE start_time <= '09:30:00' AND
end_time >= '09:30:00';

Upvotes: 1

Views: 1051

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can select everyone who is working now using:

select s.*
from shifts s
where (start_time < end_time and '09:30:00' >= start_time and '09:30:00' < end_time) or
      (start_time > end_time and ('09:30:00' >= start_time or '09:30:00' < end_time));

For employees who are not working, you use similar logic. The simplest method is:

select s.*
from shifts s
where not ((start_time < end_time and '09:30:00' >= start_time and '09:30:00' < end_time) or
           (start_time > end_time and ('09:30:00' >= start_time or '09:30:00' < end_time))
          );

Upvotes: 2

Related Questions