CODEWITHSUNDEEP
sqloracle-databasedatetimegroup-bydate-formatting
serdanarik
serdanarik

Reputation: 45

ORACLE - Group By Ten Minutes

I have a time_part columnn in my table which has time format like 13:24 . I want to group by time_part by per 10 minutes. How can I group time by 10 minutes.

Here is query:

SELECT substr(tran_time, 1, 2) || ':' || substr(tran_time, 3, 2) as time_part, recon_date

Here is my table and tran_time is='hhmmss' :

time_part  tran_time recon_date  data
10:14      101425   13/12/2015   a
22:29      222939   13/12/2015   b
22:23      222342   13/12/2015   x
00:46      004615   13/12/2015   d
16:22      162259   13/12/2015   e
12:13      121344   13/12/2015   f
12:14      121410   13/12/2015   g
12:10      121008   13/12/2015   b
21:17      211732   13/12/2015   f
20:25      202511   13/12/2015   r

Example output should be like that

trantime   count(*)
000101     5
001101     4
002101     13
.
.
.
230101     5
231101     23

Upvotes: 0

Views: 2618

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

  • Firstly, it is a bad design to store time portion separately as string.

  • Secondly, a DATE always has both date and time elements. All you need to do is extract the time portion from the date as and when required using TO_CHAR and desired format model.

In the comments you said, time_part's data type is char , recon_date's data type is date, which means, recon_date has both date and time elements. So, you do not need the SUBSTR, simply use TO_CHAR on the date column.

SELECT TO_CHAR(recon_date, 'mi'), COUNT(*) 
FROM your_table
GROUP BY TO_CHAR(recon_date, 'mi');

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Extract the hour plus 10 minute part in a derived table. Then do GROUP BY in the outer table:

select 10_min_part, count(*)
from
(
 SELECT substr(tran_time, 1, 3) as 10_min_part
 from tablename
) dt
group by 10_min_part

In the SELECT list you can concat 001 to the 10_min_part. (Right now I can't remember how Oracle does that...)

Upvotes: 2

Related Questions