Re: storing start/end times
Posted by: Peter Brawley
Date: April 09, 2015 11:10AM

> in national holidays etc..

If all the stores are in one country, here is one solution---keep a table of holidays(d date), populate it with dates for xmas, the national holiday, labour day &c, then populate the hoursopen table for each store with something like this ...

insert into storesopen
  select null, s.sid, c.d, '08:00:00', '05:00:00'
  from stores s
  cross join calendar c              -- insert a row for every combo of store row and date
  left join holidays h on c.d=h.d    -- exclude holidays
  where weekday(c.d) < 5 and h.d is null;

...left join holidays h ... where h.d is null... is an exclusion join, read about 'em, it will skip the calendar rows that have matching dates in holidays.

If the default is to have 2 open periods a day, run that again for the second open period.

Then for store owners, build a monthly calendar interface from a query like ...

select c.d, s.open,s.close
from calendar c
join storesopen s
where s.sid=_STORE_ID_ and year=2016;

> automatically the fields are populated with the current time/time of insertion.

A column created as time default null will not do that.



Edited 1 time(s). Last edit at 04/11/2015 12:48AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: storing start/end times
April 09, 2015 11:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.