Re: How to design schema for time information

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

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 Received on Thu Mar 06 2008 - 10:25:16 CST