PostgreSQL – How to Store Day and Time

postgresql

I have an events table in which various events could be happening in time slots each day. What I mean is each event has many time slots each day of the week. For example, event A can be have slots such as 1-2 pm, 5-6 pm on Mondays, 2-4 pm, 5-7 pm on Tuesdays etc.

What's a good way to structure this in postgres taking into account that queries could be on events by timings as well. For example, find all events on Monday which occur between 5 - 7 pm.

I was thinking of having a second table with event id as the first column, day of the week in the second column and then it's timing in the third column. So events having multiple timings in a day would be stored in individual rows for each timing on each day.

Alternatively, I was also thinking of a table with first two columns same as above, and a third column of array of timings, so events having multiple timings in a day would be stored in just one row for each day.

Which one is better or is there an even better way? If the former is better, in what scenario is use of arrays in databases useful? IMO, anything that can be done with arrays can be done with splitting tables or storing in different rows which are rather helpful when querying.

Also, how would I go about storing time such as 5-6 pm etc in the databse? I've a fair idea about storing the day of the week by using 7 bit boolean value. So Sunday would be 0000001, Sunday and Monday would be 0000011 etc, but how would I store the time ranges?

Thanks!

Best Answer

I would have your event table and use the event id as a foreign key to another table we might call event_times. This new table might look like this:

id  |  eventID  |  dayOfWeek  | startTime  |  endTime
--------------------------------------------------------
1      1           1            13:00:00      14:00:00

The day of week could be a smallint where 0 represents Sunday and 6 represents Saturday. Start time and end time would be the the time data type, as you only need to store the time of day (in 24-hour time).

To produce your output, your sample query would be:

SELECT eventID FROM event_times WHERE dayOfWeek = '1' AND startTime = '17:00:00' AND endTime = '19:00:00';

To get more information about the event, you would just need to join the eventID on your event table.

Related Question