--- In firebird-support@yahoogroups.com, "Paul R. Gardner"
<gardnerp@...> wrote:

>
> I'm attempting to store a weekly schedule for a shop in a Firebird 1.52
> DB.
>
> I have an open and close time each day, as well as a smallint field for
> whether the shop is actually open or not that day. Rather than have 21
> different fields I thought it would be easier to use an array from
> [dayMonday to daySunday] or [1..7].
>
> ALTER TABLE SHOP ADD OPENTIME TIME[1:7];
> ALTER TABLE SHOP ADD CLOSETIME TIME[1:7];
> ALTER TABLE SHOP ADD CLOSED SMALLINT[1:7];
>
> OK, my fields are there. Now I'm stuck. How can I reference or update
> any of this?!
>
> "select opentime[1] from shop" seems to work (although it only returns
> NULL with no data).
>
> How do I get data into this field with an update or an insert statement?
>
> Paul Gardner

The only way you would end up with 21 fields is if you had not
normalised the database. I would approach your problem like this.

Shop
(
DOW SmallInt,
OpenTime TIME,
CloseTime TIME,
)

The absence of a record for a given DOW is equivalent to the closed
flag being set. You can enhance this table to use a domains for DOW,
accepting numbers between 0 and 6 representing Sun through Sat.

You can then use EXTRACT(WEEKDAY FROM ??) to get the day you are
after. You will also want to possibly add a constraint CloseTime >=
OpenTime.

Adam