dbTalk Databases Forums  

date, select // count per 10 minutes

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc date, select // count per 10 minutes

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss date, select // count per 10 minutes in the comp.databases.oracle.misc forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Gerhard
 
Posts: n/a

Default date, select // count per 10 minutes - 09-03-2008 , 07:50 AM





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


Reply With Quote
  #2  
Old   
Michel Cadot
 
Posts: n/a

Default Re: date, select // count per 10 minutes - 09-03-2008 , 10:32 AM






"Gerhard" <gerhard_ulrike (AT) yahoo (DOT) de> a écrit dans le message de news:
21de0c2c-1e47-4853-b977-9a4181cf6566...oglegroups.com...
Quote:
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




Reply With Quote
  #3  
Old   
Michel Cadot
 
Posts: n/a

Default Re: date, select // count per 10 minutes - 09-03-2008 , 10:32 AM



"Gerhard" <gerhard_ulrike (AT) yahoo (DOT) de> a écrit dans le message de news:
21de0c2c-1e47-4853-b977-9a4181cf6566...oglegroups.com...
Quote:
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




Reply With Quote
  #4  
Old   
Michel Cadot
 
Posts: n/a

Default Re: date, select // count per 10 minutes - 09-03-2008 , 10:32 AM



"Gerhard" <gerhard_ulrike (AT) yahoo (DOT) de> a écrit dans le message de news:
21de0c2c-1e47-4853-b977-9a4181cf6566...oglegroups.com...
Quote:
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




Reply With Quote
  #5  
Old   
Michel Cadot
 
Posts: n/a

Default Re: date, select // count per 10 minutes - 09-03-2008 , 10:32 AM



"Gerhard" <gerhard_ulrike (AT) yahoo (DOT) de> a écrit dans le message de news:
21de0c2c-1e47-4853-b977-9a4181cf6566...oglegroups.com...
Quote:
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




Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top