Re: How to design schema for time information

Jason wrote:
> I am wondering if there is a better way to define a column to store
> time information.
>
> I need to create new table to represent store's open and close time
> for every weekday. i.e.
>
> create table storehours(MondayOpen time,
> MondayClose time,
> TuesdayOpen time,
> ....
> FridayOpen time,
> FridayClose time)
>
> But I just realized that Oracle doesn't have the TIME data type. And
> the DATE and TIMESTEMP don't seem to help in this situation. So an
> alternative could be:
>
> create table storehours(MondayOpen integer,
> MondayClose integer,
> TuesdayOpen integer,
> ....
> FridayOpen integer,
> FridayClose integer)
>
> But I don't like it because it is difficult to understand or use.

Don't use integers.

Use a DATE datatype. Ignore the month/day/year part of the data and just use hours minutes and (possibly) seconds.

One common trick is to use the week of Jan 1 1900 which started on Monday. That way you have the day of the week embeded in the data.

create table storehours (open DATE, close DATE)

/*monday*/

insert into storehours (open,close) VALUES (
to_date('01/01/1900 08:00am', 'mm/dd/yyyy hh:miam'),
to_date('01/01/1900 08:00pm', 'mm/dd/yyyy hh:miam'));

/*tuesday*/

insert into storehours (open,close) VALUES (
to_date('01/02/1900 08:00am', 'mm/dd/yyyy hh:miam'),
to_date('01/02/1900 08:00pm', 'mm/dd/yyyy hh:miam'));

/*wednessday*/

insert into storehours (open,close) VALUES (
to_date('01/03/1900 08:00am', 'mm/dd/yyyy hh:miam'),
to_date('01/03/1900 08:00pm', 'mm/dd/yyyy hh:miam'));

etc.

This way you can do queries like:
SELECT to_char(open, 'DAY hh:miam'), to_char(close, 'DAY hh:miam') from storehours;

The above assumes that you only want to represent the hours once and have it apply to every week.

//Walt Received on Thu Mar 06 2008 - 09:46:21 CST