Re: How to store opening time of shops?

In article <q6nla6$h6e$1_at_gioia.aioe.org>, ^Bart <gabriele1NOSPAM_at_hotmail.com> wrote:
> > And why not use TIME (see:
> > https://dev.mysql.com/doc/refman/8.0/en/time.html)
> >
> > CREATE TABLE schedule (
> >     storeid INTEGER
> > ,    dow     INTEGER
> > ,    opened  TIME
> > ,    close   TIME
> > ,       primary key(storeid, dow, opened)
>
> I'm a MariaDB newbie, I'm sorry for my question but could you write me
> an example about how I could store something like:
>
> Opening time (Monday->Friday)
> 08:30->12:30
> 15:00->19:00
>
> Opening time (Saturday)
> 08:30->12:30
>
> Closing time (Sunday)
>
> Maybe I could set seven fields in a timetable one for every week's days?
>
> Thank you very much for your patience! :)

[Quoted] [Quoted] 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');

[Quoted] 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); [Quoted] 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.

Cheers
Tony

-- 
Tony Mountifield
Work: tony_at_softins.co.uk - http://www.softins.co.uk
Play: tony_at_mountifield.org - http://tony.mountifield.org