Re: How to design schema for time information

From: Vince <vinnyop_at_yahoo.com>
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

Original text of this message