Re: How to store opening time of shops?
From: Luuk <luuk_at_invalid.lan>
Date: Sun, 17 Mar 2019 14:39:16 +0100
Message-ID: <5c8e4e01$0$22354$e4fe514c_at_news.xs4all.nl>
>
> Add a table (pseudocode):
>
> Open_schedule (
> storeid integer
> references Store(storeid)
> , open_dow integer -- 0..6 = Sun..Sat
> , open_minute integer -- 0..1439 = 00:00..23:59
> , close_minute integer -- 0..1439 = 00:00..23:59
> check (close_minute > open_minute)
> , primary key (storeid,open_dow,open_minute)
> );
>
> Add an index on Open_schedule(storeid).
> Add a trigger that checks whether opening times overlap, fail
> the transaction if they do.
>
> You will need to add refinements to allow for opening times for
> special events, and exceptions on holidays.
> I'm sure there are some examples to be found on the interwebs.
>
Date: Sun, 17 Mar 2019 14:39:16 +0100
Message-ID: <5c8e4e01$0$22354$e4fe514c_at_news.xs4all.nl>
On 17-3-2019 13:39, Kees Nuyt wrote:
> On Sun, 17 Mar 2019 12:39:22 +0100, ^Bart
> <gabriele1NOSPAM_at_hotmail.com> wrote:
>
[Quoted] [Quoted] >> I should store opening time of shops but I think I can't use just one field! >> >> There are shops opened every days from 08:00 am to 08:00 pm others >> opened just few days and not with the same time every days... :\ >> >> How could I solve this problem?
>
> Add a table (pseudocode):
>
> Open_schedule (
> storeid integer
> references Store(storeid)
> , open_dow integer -- 0..6 = Sun..Sat
> , open_minute integer -- 0..1439 = 00:00..23:59
> , close_minute integer -- 0..1439 = 00:00..23:59
> check (close_minute > open_minute)
> , primary key (storeid,open_dow,open_minute)
> );
>
> Add an index on Open_schedule(storeid).
> Add a trigger that checks whether opening times overlap, fail
> the transaction if they do.
>
> You will need to add refinements to allow for opening times for
> special events, and exceptions on holidays.
> I'm sure there are some examples to be found on the interwebs.
>
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 [Quoted] , primary key(storeid, dow, opened)
You should also check (i.e. using a TRIGGER (see: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html)) if the opening time is before the closing time.
When you choose TIME as datatype, you should also check if this field is between 00:00:00 and 23:59:59 (HH:MM:SS)
-- LuukReceived on Sun Mar 17 2019 - 14:39:16 CET