NodeJS how to execute a function only on working hours?

Question

I need to trigger a function to send mails to users only during their working hours.

The function that sends mails works and I know how to perform CRON jobs.

My different users do not have the same working hours ; for example :

User1 : [9h00;12h30] and [14h00;17h30]

User2 : [8h30;12h00] and [13h00;16h30]

etc...

and it can be different from Monday to Sunday.

If all hours were the same for all users and all days, with no lunch break, I would have a couple of keys in my MySQL DB "mailEnableHour" and "mailDisableHour" that my function would check before any action...

But the problem is actually more tricky : 7 days x 4 times = 28 keys...

Question :

How would you organize the DB to store this 28 keys for each user please ?

Do you know any way to reduce the 28 keys to a unique key ?

How to get a more "flexible" code (let's imagine that I had a new break to add in the future for example [10h30;10h45]) ?

Thank you for your help :)

Best regards.

Answer 1

What you ideally need is INTERVAL data type. But it looks like MySQL doesn't have support for this. But even with that datatype, you would need another table.

If I understand correctly, not only these hours can change per user but they can change per day for the same user.

So

User 1 Monday: [9.00 - 12.30] & [14.00 - 17.30]

User 1 Tuesday: [10.00 - 13.30] & [14.30 - 18.00]

If that's the case, your best bet is using another table - something like MailEligibleWorkHours where you can set these fields as indexes and query the users / eligibility from there. (Day being one of the columns) Perhaps your actual users table contains bunch of other fields which can help you optimize the join (like is user on vacation / on parental leave etc...).

If you want to make it even more flexible, you can have an interval table, which you'd have to reference the interval_id's to your MailEligibleWorkHours table. This design would allow you to

1. Add / remove time intervals as appropriate.
2. Re-use a time interval which was already defined (like Monday 09.00 - 12.30 slot)
3. Chances are TimeIntervals won't be a huge table, so you can query everything and cache the ids + time slots which would mean you can figure out with a single roundtrip without any joins if an employee is eligible for mail at that time.

MailEligibleWorkHours table

| employee-id | interval_id |
|:------------|:------------|
| 111         | 1           |
| 111         | 3           |
| 132         | 2           |

TimeIntervals table

| interval_id | dayOfWeek | timestart | timeend |
|:------------|:----------|:----------|:--------|
| 1           |  1        |  12.00    | 13.30   |
| 3           |  4        |  14:00    | 16:30   |
| 2           |  3        |  09:00    | 12:30   |