11

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:

  1. 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.
  2. 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:

  1. Show the user all the merchants hours, much like the store sign above, on a merchant details page.
  2. Indicate if the store was currently open.
  3. Indicate the amount of time until the store opens next.
  4. Probably other things too...

I'm looking for advice on how to represent this data in postgres.

Jonah
  • 1,436
  • 2
  • 14
  • 18
  • 1
    For those considering an answer of their own, consider: what about merchants who are open overnight, like a club or bar that opens 10pm Friday night and closes at 4am Saturday morning - then opens again Saturday at 10pm? This is actually a pretty nifty example of how the simplest thing that could possible work, upon further reflection, might not actually work. And do you need to support holidays or seasonal hours? Does this handle multiple stores, in which case what about time zones? This is also a good example of how important sample data and more complete requirements can be :) – BrianH Feb 19 '16 at 21:56
  • @BrianDHall, No, don't have to support holidays or seasonal hours. Yes, multiple merchants will be stored, and some will be in different time zones than others. I posted some sample data in the OP. When asking technical questions, I try to find a balance between clarity and information overload. I find that you can rarely please everyone, and I personally prefer others to err on the side of brief clarity and let me pull additional info out of them with followup questions. – Jonah Feb 19 '16 at 23:02

3 Answers3

6

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.

Adam Zuckerman
  • 3,715
  • 1
  • 19
  • 27
  • thanks. What are your thoughts on using postgres range types for [OpenTime, CloseTime] and [ValidFrom, ValidThru]? – Jonah Feb 20 '16 at 13:49
  • 1
    @Jonah they are neat. They are not anywhere near standard. –  Feb 20 '16 at 19:57
  • @MichaelT, I'm not sure if that is an endorsement or warning? I'm using a modern version of pg that supports them. Do you think I should use them here? – Jonah Feb 20 '16 at 20:16
  • If you ever have the possibility of moving to a different relational database, I would not use non-standard types (such as the ranges). – Adam Zuckerman Feb 20 '16 at 22:30
  • 2
    @Jonah A warning, in my opinion. Non-standard things will take longer to support than standard things by libraries such as ActiveRecord, Hibernate, etc. that act as a database abstraction layer. And as was mentioned, if you ever plan on changing databases, you'll have a much harder time doing so. As a general rule, it's good to stick to the standards. Avoid fancy platform-specific things unless you *really have a good use case*. If you can represent the same information semantically in an easier fashion, you'll save yourself future hassle. – Chris Cirefice Feb 20 '16 at 22:57
3

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

Ewan
  • 70,664
  • 5
  • 76
  • 161
  • 2
    Imagine for a bit, working at Hoover Dam. It is on the border between Nevada and New Mexico. Nevada is in Pacific time, New Mexico in Mountain time. Nevada has daylight savings, New Mexico doesn't. Oh the joys. –  Feb 20 '16 at 19:50
  • owch! This particular restaurant had its own 'week' (wed to wed) and year (first wed after xmas or something) – Ewan Feb 20 '16 at 20:13
1

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)
Jacobm001
  • 135
  • 8