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: SQL count by time intervals

Re: SQL count by time intervals

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 10 Nov 2005 07:11:35 +0100
Message-ID: <4372e495$0$607$626a14ce@news.free.fr>


Comments embedded.

"Schroeder" <schroeder915_at_yahoo.com> a écrit dans le message de news: 1131571723.970299.67360_at_g43g2000cwa.googlegroups.com...
| First, Michel, thank you for your kind assistance.
|
| I love to see things done in ways that I haven't thought of. In truth,
| I'm still trying to ascertain what it's doing.
|
| If, for example, I parse out the pieces of the statement that
| manipulate time, the results are not what I'd expect (as below).
|
| The query you wrote does very elegantly split the events into three
| discrete categories. The method appears to be to subract 7 hours,
| truncate to the hour portion of the result, and divide by 8 (there
| being three increments of 8 in a 24 hour period. I can't say I
| understand why you're substracting 7 hours.

I substract 7 hours because your first shift of the day begins at 7AM doing so i have know 3 periods [0,7[, [7,15[ and [15,24[ corresponding to your [7AM,3PM[, [3PM,11PM[ and [11PM, 7AM[. It is then easy to have the switch number dividing by 8 and keeping only the integer part.

|
| In any event, if I wish to specify particular time ranges, I don't
| believe I can get there with this.
|
| 7:00AM - 2:59PM
| 3PM - 10:59PM
| 11PM - 6:59AM
|
| So, it seems like that still takes me back to using a case statement?
|
| select date_time_rec,
| date_time_rec-7/24,
| to_char(date_time_rec-7/24,'HH24'),
| trunc(to_number(to_char(date_time_rec-7/24,'HH24'))/8) "Shift"
| from complain
| where date_time_rec
| between '06-NOV-05' and '07-NOV-05'
|
|
| DATE_TIME_REC,DATE_TIME_REC-7/24,TO_CHAR(DATE_TIME_REC-7/24,'HH24'),Shift
| 11/6/2005 12:06:00 AM,11/5/2005 5:06:00 PM,17,2
| 11/6/2005 12:06:00 AM,11/5/2005 5:06:00 PM,17,2
| 11/6/2005 12:09:00 AM,11/5/2005 5:09:00 PM,17,2

At 12:xx AM we are in third shift (=2 with first one at number 0) of the previous day

| ...
| 11/6/2005 7:01:00 AM,11/6/2005 12:01:00 AM,00,0
| 11/6/2005 7:02:00 AM,11/6/2005 12:02:00 AM,00,0
| 11/6/2005 7:03:00 AM,11/6/2005 12:03:00 AM,00,0
| ...

At 7:xx AM, we are in first shift (0=first) of the current day.

| 11/6/2005 3:00:00 PM,11/6/2005 8:00:00 AM,08,1
| 11/6/2005 3:00:00 PM,11/6/2005 8:00:00 AM,08,1
| 11/6/2005 3:02:00 PM,11/6/2005 8:02:00 AM,08,1
|

At 3:xx PM we are at second shift (=1 with first one at number 0) of the current day. If you don't like a shift of number 0 just add 1 to the shift formula.

Is this now clear ?

Regards
Michel Received on Thu Nov 10 2005 - 00:11:35 CST

Original text of this message

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