Re: Time-only column techniques

From: Karl E. Jørgensen <kjorg_at_email.msn.com>
Date: 1998/02/07
Message-ID: <u4ewovHN9GA.210@upnetnews02.moswest.msn.net>#1/1

I would go for using the DATE type and using some arbitrary agreed-upon constant for the date portion.
You could then enforce this with a check-constraint in the style of

    create table xxxx (

        my_date date not null,
        constraint xxxx_checkdate
            check (trunc(my_date) = to_date('01/01/1900','DD/MM/YYYY'))

    );

--
Hope this helps
¯`·.¸¸.·´¯`·.¸¸.·´¯`·.¸¸.·´¯`·.¸¸.·´¯`·.¸¸.·´¯
Karl
Certified Oracle DBA

Jerry Gitomer wrote in message <34D95E50.3740_at_p3.net>...


>Hi Rick,

>

>Maybe its getting late but I am confused by your posting.

>

>The Oracle DATE datatype already includes the time down to the nearest

>second and is good for something like 4712BC to 4712AD.  Since you can

>extract any part of the DATE using a format string why do you feel that

>it should be changed?

>

>If you prefer you can certainly build your own index fields by creating

>a TIME column of type NUMBER when you insert data into your table and

>then indexing TIME, but why bother?  Oracle is perfectly content to

>index on DATE datatypes and no extra work is required on your part.

>

>Regards

>

>Jerry

>

>

>Rick Jones wrote:

>>

>> Since Oracle doesn't support a "TIME" datatype, I'm wondering whether

>> there is a commonly-used workaround within the Oracle community.

>>

>> My goal is for the columns to be eligible for datetime arithmetic, date

>> functions, and for the optimizer to be able to use indexes when

>> evaluating "time" predicates.  I don't believe the "columnname -

>> trunc(columname)" technique for extracting just the "TIME" portion of any

>> DATE column would be a good solution.

>>

>> With that in mind, I'm assuming we should use a DATE datatype and store a

>> locally agreed upon constant (eg, 01-JAN-0001) into the "date" portion.

>> However, I would appreciate hearing other suggestions.

>>

>> -------------------==== Posted via Deja News ====-----------------------

>>       http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Sat Feb 07 1998 - 00:00:00 CST