MySQL: A query to work with timetables?

Question

In the project I'm working on right now the system stores employees' timetables in the table with the following structure:

employee_id | mon_h_s | mon_m_s | mon_h_e | mon_s_e | tue_h_s | tue_m_s | etc.
------------------------------------------------------------------------------
      1         06         00        14        30        06        00     ...
      2         18         30        07        00        21        00     ...

where:

mon_h_s - monday hours start

mon_m_s - monday minutes start

mon_h_e - monday hours end

mon_m_e - monday minutes end

tue_... - tuesday...

Every day of the week has 4 fields: hours start, minutes start, hours end, minutes end.

So, from the table above we can see that:

employee with the id 1 works from 06:00 to 14:30 on Monday
employee with the id 2 works from 18:30 to 07:00 on Monday (basically, between Monday and Tuesday, at night)

The problem is that I'm not sure how to create a SQL query which takes into account everything including time overlapping (at night time). For example, we need to find an employee who works at 6am (06:00) on Tuesday. In our case both employees (id 1 and id 2) would satisfy this criteria. Employee with the id 1 starts his work at 06:00 on Tuesday, and employee with the id 2 works until 07:00 Tuesday (starts on Monday though).

Any suggestions on how to solve this problem would be greatly appreciated.

No need to separate hours and minutes into their own attributes, as the SQL `TIME` type can adequately represent HH:MM:SS. — pilcrow, Nov 02 '11 at 13:46

MPelletier · Accepted Answer · 2011-11-02T01:54:40.570

Probably something like:

SELECT (1440 + ((mon_h_e*60)+mon_m_e) - ((mon_h_e*60)+mon_m_e)) % 1440

This will give you the time worked in minutes. Basically, it adds 1440 (minutes in a day, or 24h*60min/h) to the difference between end time and start time, and keep the rest (modulo) of 1440.

Now for the design part:

If you can, redesign your table. Your table need not have all days of the week in one row, that will make tallying of weekly times very tedious.

You should consider using real datetimes.

employee_id | entrytime           | exittime
          1 | 2011-10-31 06:00:00 | 2011-10-31 14:30:00
          1 | 2011-11-01 06:00:00 | null
          2 | 2011-10-31 18:30:00 | 2011-11-01 07:00:00
          2 | 2011-11-01 21:00:00 | null

That way, you have:

Full access to all date and time functions in MySQL
Easy calculation of duration
Easy filtering on incomplete periods

@AndriyM Thanks, I typed it too fast, I guess. – MPelletier Nov 01 '11 at 17:40 — MPelletier, Nov 01 '11 at 17:40

Zohaib · Answer 2 · 2011-10-31T12:33:47.410

-2

There are four basic cases that you need o handle

A -> when time of lecture starts before given time
B -> when time of lecture starts after given time but falls within range of ending time
C -> when time of lecture starts within given time but ends after
D -> when time of lecture starts before given time and ends after given time

Now, this can be accomplished using simple OR conditions

There are four basic cases that u need to handle, A, B,C and D

edited Oct 31 '11 at 12:33

answered Oct 31 '11 at 12:25

Zohaib

7,026
3
26
35

2

Are you sure this is an answer for this question? You mention "lectures" when the OP is concerned with "employee timetables". You diagram overlapping intervals when the OP is asking about point-in-time in abstract interval specifications that span day boundaries. You don't show any SQL relevant to the OP's posted schema -- those columns as designed do not lend themselves to "simple OR conditions". – pilcrow Oct 31 '11 at 20:52

MySQL: A query to work with timetables?

2 Answers2