Here is 24 hours
SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY);
or
SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR);
or your way
SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400);
Here is 48 hours
SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY);
or
SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 48 HOUR);
or your way
SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 2));
Here is 30 days
SELECT info FROM table
WHERE date > UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY);
or your way
SELECT info FROM table
WHERE date > (UNIX_TIMESTAMP(NOW()) - (86400 * 30));
Your last expression WHERE date > (UNIX_TIMESTAMP(NOW()) - 86400)
is feasible, but requires you to do the number of seconds difference. My additional suggestions lets MySQL do the date computation.
You could have a bookings table like this:
bookings
--------
id (PK)
user_id (FK to table with all medical professionals)
room_id (FK to table with all rooms)
start_date - datetime value
end_date - datetime value
This structure will let you track which professional is using which room, and when:
bookings
--------
id | user_id | room_id | start_date | end_date
---------------------------------------------------------
1 | 001 | 123 | 20140101T1030 | 20140101T1045
2 | 001 | 123 | 20140101T1100 | 20140101T1115
3 | 002 | 120 | 20140101T1000 | 20140101T1100
To track historic data, you'll probably want to use "soft" deletes on rooms and professionals so that even if you decide to delete Dr. Phillips from the list of current renters, you can still look up when he used which rooms. Your application will have to be smart enough to not create new bookings with professionals/rooms that are marked as "deleted".
professionals
-------------
id
name
deleted
(other fields...)
rooms
-----
id
deleted
(other fields)
Example:
professionals
-------------
id | name | deleted
----+------------+--------
1 | Dr. Philips| N
2 | Dr. Nick R.| Y
rooms
-----
id | deleted
----+--------
123 | N
120 | N
837 | Y
To handle recurring appointments, you could start with something like this:
recurring_bookings
------------------
id (PK)
user_id (FK to med. professionals)
room_id (FK to rooms)
day_of_week (could be FK to table/enum of days of week)
start_time
end_time (or you could have a "duration", in minutes, if you prefer)
start_of_recurrence
end_of_recurrence
recurring_bookings
------------------
id | user_id | room_id | day_of_week | start_time | end_time | start_of_recurrence | end_of_recurrence
-----+------------+-----------+-------------+------------+----------+---------------------+------------------
1 | 001 | 120 | MONDAY | 1030 | 1100 | 2014-01-01 | 2014-12-31
2 | 001 | 120 | WEDNESDAY | 1030 | 1100 | 2014-01-01 | 2014-12-31
The above example shows a user with a recurring booking every monday and wednesday in room 120, running from 10:30 to 11:00 AM. It also shows that the period over which both recurrences are defined runs from 2014-01-01 to 2014-12-31
The tricky part is handling exceptions. For example, suppose a user wants to eliminate only one instance of a recurring booking, because they'll be on vacation that week. One strategy might to think of the recurring bookings table as a rule-set which can be used to populate real bookings into the main bookings table. So that way, you use recurring_bookings
to populate all appointments for a user in bookings
, and then rmeove or modify them from bookings
as needed. This requires you to pre-populate a lot of data into your main bookings
table (based on start_of_recurrence
and end_of_recurrence
), which you may or may not want to do, for recurring appointments. To handle open-ended recurring appointments, it might require a bit more work. Also, the above example assumes all recurring bookings are weekly. Some of them may be daily or yearly or monthly, and may have conditions such as "first Wednesday of every month".
I will admit that I haven't tried to build a system with recurring bookings like this, so this design should be considered a suggestion for a starting point. It will probably need some work. To get an idea of one way this is done, try creating and then manipulating recurring appointments in Outlook (if you have access to it).
Best Answer
I would store the opening hours per weekday/per company. For storing the actual hours, a timerange seems suitable.
I would use 1 for Monday because that's the ISO definition and most of Postgres' date functions work like that, so you don't need to do conversion from and to a different base number for Monday.
If there is a noon break, it would mean that the
day_of_week
gets two entries, e.g.To find the companies that are opened at a specific day and time you can use something like this:
An exclusion constraint can be used to avoid overlapping opening hours for the same day and company.