Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Time-only column techniques

Re: Time-only column techniques

From: Rick Jones <rick.jones_at_kaisertwd.com>
Date: 1998/02/12
Message-ID: <887267070.379191297@dejanews.com>#1/1

Hi Jerry,

I'll try to clarify my point. There are times when it may be desirable to store a "time-of-day" without regard to a specific date, such as the start & end times for each "shift" definition, or the normal depart & arrival times for each flight.

As I stated before, 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. By using the DATE datatype I can use datetime arithmetic and date functions, regardless of the "date" portion. However, I don't believe an INDEX on the column would be used to find all rows between 08:00 and 09:00 unless the predicate can include the date portion (and that date must then be in all rows).

I hope that explanation is clearer.

...Rick

In article <34D95E50.3740_at_p3.net>,
  jgitomer_at_p3.net wrote:
>
> 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

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Thu Feb 12 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US