Get the currently online employees using MySQL

Question

I have employees working in different shifts and weekdays. Their shift details are saved in workshifts table in the following format.

1. WeekDay - Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
2. StartTime - 24Hr format. For ex, 05:00
3. EndTime - 24Hr format. For ex, 18:00

Some employees work in night shifts. So, their start time would be 20:00 & end time would be 05:00 (next day morning). For night shift employees, the WeekDay would be the Starttime weekday.

I need to show the employees online/working now. This is a real-time data. I am currently extracting the records for the current day & then using Java validating if the current time is within Start & End time. If yes, then the record is displayed, otherwise ignored.

This entire process is taking too long time now due to the increase in employees.

I would like to do this check directly using SQL. StartTime & Endtime fields are varchar(10) and will always have the format HH:MM

Is it possible to do this directly within SQL?

Answer 1

It really would be easier if you stored the actual times using a date/time format. But, given the format of the data, you can just use explicit logic to figure out who is currently in their shift:

select ws.*
from workshifts ws
where (ws.StartTime < ws.EndTime and
       date_format(now(), '%h:%i') >= ws.StartTime and 
       date_format(now(), '%h:%i') <= ws.EndTime and
       date_format(now(), '%W') = ws.Weekday
      ) or 
      (ws.StartTime > ws.EndTime and
       ((date_format(now(), '%h:%i') >= ws.StartTime and 
         date_format(now(), '%W') = ws.Weekday
        ) or
        (date_format(now(), '%h:%i') <= ws.EndTime and
         date_format(date_sub(now(), interval 1 day), '%W') = ws.Weekday
        )
       )
      );