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.