Re: How to store opening time of shops?

From: Luuk <luuk_at_invalid.lan>
Date: Mon, 18 Mar 2019 19:57:06 +0100
Message-ID: <5c8fe9fe$0$22349$e4fe514c_at_news.xs4all.nl>


On 18-3-2019 14:33, ^Bart wrote:

>> If, for example, the store in question has a storeid of 123:
>>
>> INSERT INTO schedule (storeid, dow, opened, close) VALUES
>> (123, 1, '08:30', '12:30'), (123, 1, '15:00', '19:00'),
>> (123, 2, '08:30', '12:30'), (123, 2, '15:00', '19:00'),
>> (123, 3, '08:30', '12:30'), (123, 3, '15:00', '19:00'),
>> (123, 4, '08:30', '12:30'), (123, 4, '15:00', '19:00'),
>> (123, 5, '08:30', '12:30'), (123, 5, '15:00', '19:00'),
>> (123, 6, '08:30', '12:30');

>
> It's a real good idea! :)
>
>> If you explicitly want a record for Sunday to indicate closed, you 
>> could do:
>>
>> INSERT INTO schedule (storeid, dow, opened, close) VALUES
>> (123, 0, NULL, NULL);

>
> Good!!!
>
>> In that case, you would need to delete the record that contains NULLs 
>> if you
>> added opening times for that day of the week (or update the record 
>> instead).
>>
>> I would probably not store records for closed days, and just infer closed
>> from the lack of any opening time records.

>
> Sometimes it happen to see a shop opened also during a closed day in
> this case on sunday this thing is known as "special opening days like in
> 08/12 24/12 etc."
>
> Sometimes it happen also to see a closed day during the working week for
> example for the national day, I mean I should store a field named notes
> where I could read "That day is opened, that day is closed"
>
>> Cheers
>> Tony

>
> Thank you very much for your kind reply! :)
> ^Bart

For holidays (or 'other-than-usual-days') you could define an extra table which would hold the deviations:

[Quoted] CREATE TABLE holiday (

     storeid INTEGER
,    holiday     DATE
,    opened  TIME
,    close   TIME
,       primary key(storeid, dow, opened)

INSERT INTO schedule (storeid, holiday, opened, close) VALUES

    (123, '2019-12-24', '08:30', '12:30')

-- 
Luuk
Received on Mon Mar 18 2019 - 19:57:06 CET

Original text of this message