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>
6 )
7 select to_char(h,'DD/MM/YYYY HH24:MI:SS') h,
11 order by h
12 /
03/09/2008 18:32:48 03/09/2008 18:30:00
03/09/2008 23:30:14 03/09/2008 23:30:00
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