Basic Query Help

[aswolff]

Hello,

I have a table that has several columns including 1 for date and 1 for time HHMMSS.

I would like to get a count of rows grouped by date and within a given time interval of say 5 minutes during a 24 hour period. Can somebody help?

Something like this

Count, Date, Time
1, 09/22/08,00-04
3, 09/22/08,05-09
11,09/22/08,10-14
.
.
.
.

Can somebody point me in the right direction?
Thank You!!

 

[Dagon]

Can you clarify what you mean by "within a given time interval of say 5 minutes during a 24 hour period". Do you mean you want to divide the output into groupings of 5 minutes or do you just mean you want to restrict the query to a particular 5 minute period somewhere during the day ?

 

[SantaMufasa]

...And while you are preparing a response to Dagon's excellent question, I'll add a comment that in the Oracle World, Date/Time data should always reside in a DATE datatype column. Doing such allows for Date/Time filtering, comparison, and formatting that is otherwise unwieldy and inefficient if such data resides in VARCHAR2 columns.

Many times people working under such an environment respond: "I have no control over the data design...I just have to live with it."

To that, I respond, "If the data were incorrect, would you just live with it, or would you fix it.

In my mind, storing Date/Time information in a non-DATE type column is virtually as bad as living with incorrect data.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[aswolff]

Ideally I'd like grouping of X minute intervals beginning at 12 midnight for the entire day so when I run it it should produce (24x60)/5 rows.

The Date Portion on my table is in Date format. But the time portion is a numeric(6). So 2:55:34 PM is showing as 145534.

I get the feelin this is not going to be a "Basic" query as I had hoped..anyway any ideas are appreciated.

 

[SantaMufasa]

So 2:55:34 PM is showing as 145534.

What does 145,534 represent? It's not seconds since the beginning of the day (which would be 53,734).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[aswolff]

145534 represents 2:55:34 PM

 

[SantaMufasa]

What we've got here is a failure to communicate.

I understand, ASWolf that you assert that 145,534 represents 2:55:34 PM, but how did 145,534 become 2:55:34 PM? For example, What does the value 100,000 represent in clock time, and how did it become such?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[karluk]

Try reading it in military time, SantaMufasa.

145,534 = 14:55:34 = 2:55.34 p.m.

 

[SantaMufasa]

145,534 = 14:55:34 = 2:55.34 p.m.

An "Aha!" moment. (Why didn't I see that?)


Thanks, Karl.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[aswolff]

Thank you.

 

[Dagon]

Santa is quite correct in that you have made life difficult for yourself by not storing the dates as DATES and you will realize why when you try to solve this problem.

You first need to simulate a table with the dates in 5 minute intervals. A query like this will do it:

select trunc(sysdate)+ (5/1440*(level-1)) as interval_start, trunc(sysdate) + (5/1440*(level)) as interval_end
from dual
connect by level <= 288

You then need to join back to this table to work out which rows fall into which 5 minute interval and that's when it gets difficult because you're not using dates. You will have to convert your char and integer fields into proper DATES before you can do the join.

As an incentive to use correct data types in future, I'll leave you to do the conversions. But if you had used proper dates, the format of the query would be:

create table entry_dates (entry_date date);

insert into entry_dates values (to_date('23-sep-08 00:03:12', 'DD-MON-YY HH24:MI:SS'));
insert into entry_dates values (to_date('23-sep-08 00:04:19', 'DD-MON-YY HH24:MI:SS'));
insert into entry_dates values (to_date('23-sep-08 00:05:00', 'DD-MON-YY HH24:MI:SS'));
insert into entry_dates values (to_date('23-sep-08 00:05:01', 'DD-MON-YY HH24:MI:SS'));
insert into entry_dates values (to_date('23-sep-08 12:03:12', 'DD-MON-YY HH24:MI:SS'));
insert into entry_dates values (to_date('23-sep-08 14:55:37', 'DD-MON-YY HH24:MI:SS'));
insert into entry_dates values (to_date('23-sep-08 19:11:55', 'DD-MON-YY HH24:MI:SS'));

select count(e.entry_date), i.interval_start, i.interval_end
from entry_dates e,
(select trunc(sysdate)+ (5/1440*(level-1)) as interval_start, trunc(sysdate) + (5/1440*(level)) as interval_end
from dual
connect by level <= 288) i
where e.entry_date (+) >= i.interval_start
and   e.entry_date (+) < i.interval_end
group by i.interval_start, i.interval_end