Re: dates in index

In article <388f9454.754393409_at_news.erols.com>,

        jxs_at_walaw.com (Jay Scheiner) writes:
> I have a table with a date, where I store data with both date & time
> info (1/1/99 3:30pm). I have in the range of 50-150K records per day.
> I would like to be able to select records for just a day or range of
> days without doing a full-table search.
>
> What is the best way to index this? Should I make a separate column
> with just the date? I would like to keep a column with both the date
> & time together for queries that look at date/time in a sequence.
> Total disk space isn't a big issue at this point.
>
> So far I can do 'select * ..... where trunc(date_column) = '1/1/99';
> I am not sure if this is using the index or not, I don't have enough
> days data in there, and it is a fast machine.

This will not use an index.

Basically you can do this in you query: where date_column >= trunc(:date_var)
and date_column < trunc(:date_var) + 1

The trunc() around :date_var is not necessary if you know :date_var never contains a time component (or rather, that the time component is always zero).

Do not use this:
where date_column between trunc(:date_var) and trunc(:date_var)-1/(60*60*24) This is ugly and not even entirely correct.

Another possibility is to store the date and time in two separate columns. You can store the time (in seconds past midnight) in a number(6) column. For conversion you can use the 'SSSSS' format specifier. to_number(to_char(to_date('20:23:43','HH24:MI:SS'),'SSSSS')) converts time (in character format) to number, to_char(to_date(to_char(73423),'SSSSS'),'HH24:MI:SS') converts it back.

In Oracle Forms, if you specify the datatype TIME for a field that is linked to a NUMBER column, Forms does the conversion for you.

Remco
--
rd1936: 11:55pm up 18 days, 4:35, 10 users, load average: 2.63, 2.42, 2.27 Received on Wed Jan 26 2000 - 17:21:59 CST