Re: Is this a good use for an IOT type table or are there better solutions.

On 24 Mar 2004 12:52:04 -0600, colin_lyse_at_yahoo.com (colin_lyse) wrote:

>In article <406192C8.2DC4B560_at_remove_spam.peasland.com>, Brian Peasland <dba_at_remove_spam.peasland.com> wrote:
>>> we have a table with following structure
>>>
>>> ID NUMBER NOT NULL,
>>> DATESTAMP DATE NOT NULL,
>>> HOUR NUMBER NOT NULL,
>>> AAMP NUMBER,
>>> BAMP NUMBER,
>>> CAMP NUMBER,
>>> CAT NUMBER
>>>
>>> it has a unique index on ID, DATESTAMP, HOUR. it is used for access and
>>> aslo enforces uniqueness
>>
>>Silly question, but why is there a column called HOUR when there is a
>>DATE column as well? The DATE column will hold the date and the time,
>>including the hour. IS the HOUR column redudant or is there another use?
>>
>>Cheers,
>>Brian
>>
>>
>we get a date no hour and hour separtely
>
>App that uses table expects date and hour separtely.

Without knowing any more, I'd still question keeping the hour separately from the date. Does the hour referred to in the 'hour' column occur on the date referred to in the 'date' column? If so, they belong together in the same 'date' column'. (BTW, I've always thought that calling that datatype a 'date' to be a misnomer. A date can exist without a time, but a time -- as in hh:mm:ss -- generally has no meaning without a date. So I think that datatype is more properly called 'time'. But then, they never asked me! ) Just because they are stored in the same column doesn't mean the app can't deal with them individually:

Select to_char(my_date_col,'dd-mon-yy') as "date",

             to_char(my_date_col,'hh24:mi:ss') as "time" from . . . .

Insert into .....
values (to_date(date_bind_variable,'dd-mon-yyyy'),

              to_date(time_bind_variable,'hh24:mi:ss'))

or

insert into . . .
values (to_date(date_time_bind_var,'dd-mon-yyyy hh24:mi:ss))

If you already knew this, please accept my apology. I find myself still having to teach it to apps programmers around here. Partly because they learned on DB2, where there are separate date and time datatypes. Received on Thu Mar 25 2004 - 13:56:47 CST