Results 1 to 7 of 7

Thread: opening hours

  1. #1
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692

    opening hours

    I'm struggling to get my head around how to model opening hours. happy with the various entities upstream but its how you find a mechanism which is searchable and flexible enough to cater for the various opening hours. its for a searchable website ie where it the nearest whatever that is open.

    So an entity has opening hours, there could be multiple opening hours per day (eg 09:00 12:00; 13:00 to 18:00)
    those hours may vary from day to day (eg Weekdays 08:00 ->17:00, Sat 08:12:00, Sunday shut)
    a place may have different hours per season / month / phase of the moon / physical state of the owner.. who knows.

    Im happy on the daily opening hours, say the entity alwasy opens at the same times day in day out, or the same times for specified days) its the seasonal bit that Im really struggling with. ferinstance hours may be 12:00-> 15:00 & 18:00->22:00 April -> September, but outseid ehtat 18:00->21:00. or only opens in Sundays in Summer... y'get the picture

    the data is required for a web service, but will not be provided by the organisation itself or by a central authority within the website company. its user contributed data so an additioanl problems is that we don't have control of the data or a reputable source. So it has to be easy for users to define the hours. yes you can do processing in background to con or deflate the design.

    so if you define it according to specific dates, someone has to go n and extend / replace the data as required. granted you could send an email to the entity and say 'would you like to update your hours....." but you can't guarantee that the website has the right details or the entity itself will bother updating the site. I can't guarantee that the website will be sharp enough such that the enitity will want / need to update their hours.

    if you define it as, say seasonal then you hit the porblem in going forward as some places may decide to change their hours according to another trigger eg a specific public holiday, and or DST)

    I suppose I coudl go down the route of writing 4 seasonal opening hours classifications as per the switch to winter/spring/summer/autumn.

    any thoughts?
    has anyone else already encountered this problem?
    is there a simplistic way of modelling it.?

    I did breifly think that this could be a candidate for a EVA type definition but then gave myself a good slapping and told to start over
    I'd rather be riding on the Tiger 800 or the Norton

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's another slap

    you say it's "user-contributed" but have you given any thought to just how the users are going to submit it?

    i see this as a parallel problem, in that if it's as simple as a text box, somebody will enter "half nine till four except weekends and bank holidays" and now someone (?) will have to transcribe this into whatever data structure you end up creating...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I was thinking of using some form of slide control. Its part of the complexity. if I could just store the data as recieved then I'd bung it in the db as free form text and let someone decode it for themselves (or use a text template). its how to handle the different variations of opening hours that is flummoxing me at present
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The way that I'd do it is to have two different kinds of timespans in the same row.

    One span would indicate a date range that could be a season, infinity, or anything in between. The other would be an open/close span (which could easily span days, such as opening at 16:00 and closing at 02:00 which is common for bars, etc). Each row would have a repeat frequency in terms of period (day, week, month, etc) and count (1-n).

    Using SQL to demonstrate, I'd do something like:
    Code:
    CREATE TABLE #foo (
       span_begin   DATETIME    NOT NULL
    ,  span_end     DATETIME    NOT NULL
    ,  units        VARCHAR(20) NOT NULL
    ,  cycle        INT         NOT NULL
    ,  store_open   DATETIME    NOT NULL
    ,  store_close  DATETIME    NOT NULL
       }
    
    INSERT INTO #foo  --  This is the bank's schedule
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-02 08:00', '2012-01-02 17:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-03 08:00', '2012-01-03 17:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-04 08:00', '2012-01-04 17:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-05 08:00', '2012-01-05 17:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-06 08:00', '2012-01-06 17:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-07 08:00', '2012-01-07 12:00'
    
    INSERT INTO #foo  --  This is the bar's schedule
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-02 16:00', '2012-01-03 02:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-03 16:00', '2012-01-04 02:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-04 16:00', '2012-01-05 02:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-05 16:00', '2012-01-06 02:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-06 12:00', '2012-01-07 02:00' UNION ALL
       SELECT '2012-01-01', '4000-01-01', 'week', 1, '2012-01-07 10:00', '2012-01-08 00:30'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    thanks for that
    I'm not sure its 'the' solution, but if nothing else it is provoking me into trying to get a solution rather than sit on the fence and work on other bits.

    ..at least it was mor ehelp than the lederhosen clad ones response
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    One thing that I forgot to mention was that I'd either support nested spans or would have an "exceptions" table to deal with holidays, etc... You could enter a specific date range into the exception table to override the generic schedule.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    You could enter a specific date range into the exception table to override the generic schedule.
    yeah, he could, except he doesn't wanna...
    Quote Originally Posted by healdem
    its user contributed data so an additioanl problems is that we don't have control of the data or a reputable source. So it has to be easy for users to define the hours.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •