Re: How to design schema for time information
Date: Thu, 6 Mar 2008 09:38:38 -0800 (PST)
Message-ID: <b6c66818-9275-4775-a3e5-7c3612c850cc@d4g2000prg.googlegroups.com>
On Mar 6, 8:25 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 5, 5:53 pm, "phil_herr..._at_yahoo.com.au"
>
> <phil_herr..._at_yahoo.com.au> wrote:
> > Time values - as in time of day - are easily expressed as an INTERVAL.
>
> > -- Phil
>
> Yet you provide no examples illustrating this. I prefer a view;
> creating the base table with DATE columns, and using to_char() to
> format the view data a usable format is attained:
>
> SQL> create table storehours(
> 2 MondayOpen date,
> 3 MondayClose date,
> 4 TuesdayOpen date,
> 5 TuesdayClose date,
> 6 WednesdayOpen date,
> 7 WednesdayClose date,
> 8 ThursdayOpen date,
> 9 ThursdayClose date,
> 10 FridayOpen date,
> 11 FridayClose date,
> 12 SaturdayOpen date,
> 13 SaturdayClose date,
> 14 SundayOpen date,
> 15 SundayClose date
> 16 );
>
> Table created.
>
> SQL>
> SQL> insert into storehours
> 2 values
> 3 (trunc(sysdate) + 9/24,
> 4 trunc(sysdate) + 21/24,
> 5 trunc(sysdate) + 9/24,
> 6 trunc(sysdate) + 21/24,
> 7 trunc(sysdate) + 9/24,
> 8 trunc(sysdate) + 21/24,
> 9 trunc(sysdate) + 9/24,
> 10 trunc(sysdate) + 21/24,
> 11 trunc(sysdate) + 9/24,
> 12 trunc(sysdate) + 21/24,
> 13 trunc(sysdate) + 9/24,
> 14 trunc(sysdate) + 21/24,
> 15 trunc(sysdate) + 12/24,
> 16 trunc(sysdate) + 19/24);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> create or replace view storehours_view
> 2 as
> 3 select 'Monday' day, to_char(mondayopen, 'HH:MI:SS AM') open,
> to_char(mondayclose, 'HH:MI:SS AM') close, 1 sort_ord
> 4 from storehours
> 5 union
> 6 select 'Tuesday', to_char(tuesdayopen, 'HH:MI:SS AM'),
> to_char(tuesdayclose, 'HH:MI:SS AM'), 2 sort_ord
> 7 from storehours
> 8 union
> 9 select 'Wednesday', to_char(wednesdayopen, 'HH:MI:SS AM'),
> to_char(wednesdayclose, 'HH:MI:SS AM'), 3 sort_ord
> 10 from storehours
> 11 union
> 12 select 'Thursday', to_char(thursdayopen, 'HH:MI:SS AM'),
> to_char(thursdayclose, 'HH:MI:SS AM'), 4 sort_ord
> 13 from storehours
> 14 union
> 15 select 'Friday', to_char(fridayopen, 'HH:MI:SS AM'),
> to_char(fridayclose, 'HH:MI:SS AM'), 5 sort_ord
> 16 from storehours
> 17 union
> 18 select 'Saturday', to_char(saturdayopen, 'HH:MI:SS AM'),
> to_char(saturdayclose, 'HH:MI:SS AM'), 6 sort_ord
> 19 from storehours
> 20 union
> 21 select 'Sunday', to_char(sundayopen, 'HH:MI:SS AM'),
> to_char(sundayclose, 'HH:MI:SS AM'), 7 sort_ord
> 22 from storehours
> 23 order by sort_ord;
>
> View created.
>
> SQL>
> SQL> select day, open, close
> 2 from storehours_view;
>
> DAY OPEN CLOSE
> --------- ----------- -----------
> Monday 09:00:00 AM 09:00:00 PM
> Tuesday 09:00:00 AM 09:00:00 PM
> Wednesday 09:00:00 AM 09:00:00 PM
> Thursday 09:00:00 AM 09:00:00 PM
> Friday 09:00:00 AM 09:00:00 PM
> Saturday 09:00:00 AM 09:00:00 PM
> Sunday 12:00:00 PM 07:00:00 PM
>
> 7 rows selected.
>
> SQL>
>
> Of course if you can illustrate this using INTERVAL I'd be happy to
> see it.
>
> David Fitzjarrell
You may also want to consider normalizing:
create table store_hours(
day_of_week varchar2,
day_sequence integer,
start_time date,
end_time date );
insert into store_hours
values ('Sunday', 1, trunc(sysdate) + 9/24, trunc(sysdate) + 17/24);
...
makes the selecting of hours alot less tedious:
SELECT day_of_week, to_char(start_time, 'HH:MI:SS AM') as start_time,
to_char(end_time, 'HH:MI:SS AM') as end_time
from store_hours
order by day_sequence;
Can't day_of_week and day_sequence be combined, using something that can be interpreted by to_char's 'D' and 'DDD' paramters? Received on Thu Mar 06 2008 - 11:38:38 CST