Re: date, select // count per 10 minutes

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 3 Sep 2008 17:32:24 +0200
Message-ID: <48beae09$0$25154$426a74cc@news.free.fr>

"Gerhard" <gerhard_ulrike_at_yahoo.de> a écrit dans le message de news: 21de0c2c-1e47-4853-b977-9a4181cf6566_at_d1g2000hsg.googlegroups.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 Received on Wed Sep 03 2008 - 10:32:24 CDT

Original text of this message