Re: How to design schema for time information

From: <phil_herring_at_yahoo.com.au>
Date: Thu, 6 Mar 2008 14:55:34 -0800 (PST)
Message-ID: <393ac440-61ee-4bfd-b898-b2ebaece6e94@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

Received on Thu Mar 06 2008 - 16:55:34 CST