In postgres, I need to store the hours a merchant is open each week. Essentially, the information you’d see posted on a sign in the merchant’s door:
Mondays: Closed
Tuesday-Friday: 9am-1pm, 2pm-6pm
Weekends: 10am-2pm
Some ideas so far:
- Break down a week into 30 minute (or even 5 minute) chunks, and use a bit string type to store whether the merchant is open during that chunk.
- Use 2-dimensional array type, an array “days”, with each “day” and array of hour ranges when the store was open.
Perhaps relevant is what the data would be used for:
- Show the user all the merchants hours, much like the store sign above, on a merchant details page.
- Indicate if the store was currently open.
- Indicate the amount of time until the store opens next.
- Probably other things too…
I’m looking for advice on how to represent this data in postgres.
2
The simplest storage would be:
StoreID integer (foreign key)
DayOfTheWeek integer (range 0-6)
OpenTime time
CloseTime time
ValidFrom date (starting date when this record is in use)
ValidThru date (ending date when this record is in use, NULL for no ending date)
All of the above fields should be required (NOT NULL). ValidThru could be NULL depending on how you want to mark a record as being open ended.
At some point, you will need to consider specialty days (usually related to a holiday) that may need a date field instead of the DayOfTheWeek field. If you do, remember to apply this data after you build the open hours from the storage above.
5
I programmed a system for working out shifts for a fast food outlet where we had the same problem.
I think you have missed the hardest problem which is timezones and daylight savings.
If you only have to print the information then the ‘time without timezone’ data type should be fine, but for any calculation of hours open or similar you should beware edge cases.
We resorted to storing the hour and minute as ints. This gave a clear indication of the ‘printable times’ but prevented any misuse of date time functions.
Where such calculations were required we first converted back to UTC in the relevant timezone in the relevant date.
Also, you may need to consider mutiple opening and closings in a ‘day’. For a full normalised structure you would need:
[Store]
Id
Timezone
[Day]
Id
StartDate
StoreId
[OpenPeriod]
DayId
OpenHour
OpenMinute
CloseHour
CloseMinute
Note: (im remembering more now) ‘days’ can be longer than 24h
Hours are ‘the hour the clock says’ not hour since midnight or anything
2
I would store the data as a series of start and stop times, in which each period can have multiple entries. You can setup the period as a string in which the “period” is anything you want to be, or is a defined day.
Your code will need to ensure that you aren’t entering overlapping data, but reading from this system would be very simple.
table:
field name | field type
------------------------------------------------
period | string (ex: Monday, T/TH, Weekends)
start time | time (ex: 01am; 02:30pm)
end time | time (ex: 01am; 02:30pm)