Re: How to store opening time of shops?

[Quoted] On Sun, 17 Mar 2019 12:39:22 +0100, ^Bart <gabriele1NOSPAM_at_hotmail.com> wrote:

> 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?

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

-- 
Regards,
Kees Nuyt