for statistics i need to select the number of orders per 10 minutes.

So i want to do something like
SELECT TRUNC(insert_date, '??'), COUNT(*)
FROM orders .... ;

But within the trunc-(or other data-based) function allowed Patterns
are "HH" or "MI".

as workaround i use:
SELECT TRUNC(insert_date,'HH') +
TRUNC(TO_CHAR(ord_close_dt,'MI'),-1)/(60*24), COUNT(*)
FROM orders .... ;


Is there an easyer way to do the select?

Thanks
Gerhard

"Gerhard" <gerhard_ulrike@yahoo.de> a écrit dans le message de news:
21de0c2c-1e47-4853-b977-9a4181cf6566...oglegroups.com...
| for statistics i need to select the number of orders per 10 minutes.
|
| So i want to do something like
| SELECT TRUNC(insert_date, '??'), COUNT(*)
| FROM orders .... ;
|
| But within the trunc-(or other data-based) function allowed Patterns
| are "HH" or "MI".
|
| as workaround i use:
| SELECT TRUNC(insert_date,'HH') +
| TRUNC(TO_CHAR(ord_close_dt,'MI'),-1)/(60*24), COUNT(*)
| FROM orders .... ;
|
|
| Is there an easyer way to do the select?
|
| Thanks
| Gerhard
|

SQL> with
2 data as (
3 select trunc(sysdate)+dbms_random.value(0,86400)/86400 h
4 from dual
5 connect by level <= 10
6 )
7 select to_char(h,'DD/MM/YYYY HH24:MI:SS') h,
8 to_char(trunc(h)+trunc(to_char(h,'SSSSS')/600)/144,
9 'DD/MM/YYYY HH24:MI:SS') "10m"
10 from data
11 order by h
12 /
H 10m
------------------- -------------------
03/09/2008 02:41:01 03/09/2008 02:40:00
03/09/2008 04:01:07 03/09/2008 04:00:00
03/09/2008 04:25:44 03/09/2008 04:20:00
03/09/2008 08:20:07 03/09/2008 08:20:00
03/09/2008 10:02:51 03/09/2008 10:00:00
03/09/2008 15:57:34 03/09/2008 15:50:00
03/09/2008 16:56:32 03/09/2008 16:50:00
03/09/2008 18:02:26 03/09/2008 18:00:00
03/09/2008 18:32:48 03/09/2008 18:30:00
03/09/2008 23:30:14 03/09/2008 23:30:00

10 rows selected.

Regards
Michel