Re: How to design schema for time information

<phil_herring_at_yahoo.com.au> schreef in bericht news:393ac440-61ee-4bfd-b898-b2ebaece6e94_at_s8g2000prg.googlegroups.com...
> On Mar 7, 3:25 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>> Of course if you can illustrate this using INTERVAL I'd be happy to
>> see it.
>
> A time of day, in the absence of a specific date, is defined as a
> period of time after midnight. In my opinion, that makes INTERVAL the
> most specific datatype available for this task.
>
> Using a DATE works, in the same way that using NUMBER will accommodate
> an integer. However, if you know the value is an integer, wouldn't you
> use INTEGER?
>
> SQL> create table opening_hours
> 2 (
> 3 office_name varchar2(10),
> 4 the_day varchar2(10),
> 5 opening_time interval day to second (0),
> 6 closing_time interval day to second (0)
> 7 );
>
> Table created.
>
> SQL> insert into opening_hours values ('Sydney', 'Monday',
> to_dsinterval('0 09:00:00'), to_dsinterval('0 17:00:00'));
> 1 row created.
> SQL> insert into opening_hours values ('Sydney', 'Tuesday',
> to_dsinterval('0 09:00:00'), to_dsinterval('0 17:00:00'));
> 1 row created.
> SQL> insert into opening_hours values ('Sydney', 'Wednesday',
> to_dsinterval('0 09:00:00'), to_dsinterval('0 17:00:00'))
> 1 row created.
> SQL> insert into opening_hours values ('Sydney', 'Thursday',
> to_dsinterval('0 09:00:00'), to_dsinterval('0 21:00:00'));
> 1 row created.
> SQL> insert into opening_hours values ('Sydney', 'Friday',
> to_dsinterval('0 09:00:00'), to_dsinterval('0 17:00:00'));
> 1 row created.
> SQL> insert into opening_hours values ('Sydney', 'Saturday',
> to_dsinterval('0 09:00:00'), to_dsinterval('0 12:00:00'));
> 1 row created.
>
> At this point, most people could probably read the table without any
> further assistance:
>
> SQL> select
> 2 office_name,
> 3 the_day,
> 4 opening_time,
> 5 closing_time
> 6 from
> 7 opening_hours;
>
> OFFICE_NAM THE_DAY OPENING_TIME CLOSING_TIME
> ---------- ---------- -------------------- --------------------
> Sydney Monday +00 09:00:00 +00 17:00:00
> Sydney Tuesday +00 09:00:00 +00 17:00:00
> Sydney Wednesday +00 09:00:00 +00 17:00:00
> Sydney Thursday +00 09:00:00 +00 21:00:00
> Sydney Friday +00 09:00:00 +00 17:00:00
> Sydney Saturday +00 09:00:00 +00 12:00:00
>
> 6 rows selected.
>
> The rest is just formatting. For example:
>
> SQL> select
> 2 office_name,
> 3 the_day,
> 4 trim(to_char(extract(hour from opening_time), '09')) ||
> ':' || trim(to_char(extract(minute from opening_time), '09'))
> opening_time,
> 5 trim(to_char(extract(hour from closing_time), '09')) ||
> ':' || trim(to_char(extract(minute from closing_time), '09'))
> closing_time
> 6 from
> 7 opening_hours;
>
> OFFICE_NAM THE_DAY OPENING_TIME CLOSING_TIME
> ---------- ---------- -------------------- --------------------
> Sydney Monday 09:00 17:00
> Sydney Tuesday 09:00 17:00
> Sydney Wednesday 09:00 17:00
> Sydney Thursday 09:00 21:00
> Sydney Friday 09:00 17:00
> Sydney Saturday 09:00 12:00
>
> 6 rows selected.
>
>
> -- Phil

Phil,

Would it be possible to use the first 0 in to_dsinterval('0 09:00:00') to store the day as a number offset from e.g. monday? So for tuesday use to_dsinterval('1 09:00:00')? Do I understand this correctly?

Thanks,

Shakespeare Received on Fri Mar 07 2008 - 04:44:23 CST