CODEWITHSUNDEEP
sqldatabaseoracle-databasedatetime
ea1987
ea1987

Reputation: 25

SQL Searching multiple interval times on ORACLE DB

I'm doing a query on a table in which I have to count all records on different 1 hour interval time (eg from 13:00:00 to 14:00:00). What I'm doing right now is like this:

select count (*)
from tabel
where TO_CHAR(ins_ts, 'DD-MON-YYYY HH24') like ('02-GIU-2015 13');

RESULT: 23

select count (*)
from tabel
where TO_CHAR(ins_ts, 'DD-MON-YYYY HH24') like ('02-GIU-2015 14');

RESULT: 25

But it's too much effort for doing that for all 1 hour intervals of all days of a week.. Is there a way to make a query that would return all results splitted on diffent time interval at least of each day, like this:

RESULT: 23, 25 and so on

Upvotes: 1

Views: 233

Answers (3)

TheMadDBA
TheMadDBA

Reputation: 436

If you actually want to use an index on the datetime you need to stop using functions on ins_ts (in the where clause).

select TO_CHAR(ins_ts, 'DD-MON-YYYY HH24'),count (*)
 from table where 
 ins_ts >= TO_DATE('01/JAN/2015 00:00:00', 'dd/mon/yyyy HH24:MI:SS')
 and ins_ts < TO_DATE('08/JAN/2015 00:00:00', 'dd/mon/yyyy HH24:MI:SS')
 group by TO_CHAR(ins_ts, 'DD-MON-YYYY HH24');

This should give you each hour and a count for every record between the 1st of January and the 7th. (note the second ins_ts check is using less than and not less than or equal to).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You want to truncate the time value to the nearest hour and do a count. You have the basic components in your query, you just want a group by:

select TO_CHAR(ins_ts, 'DD-MON-YYYY HH24') as thehour, count(*)
from tabel
group by TO_CHAR(ins_ts, 'DD-MON-YYYY HH24')
order by min(ins_ts) ;

The order by min(ins_ts) puts the values in order by time, because your preferred output format does not have a natural sort order.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could use GROUP BY to group the count for each interval.

For example,

SQL> WITH DATA AS(
  2  SELECT TO_DATE('06/12/2015 13:00:00','MM/DD/YYYY HH24:MI:SS', 'nls_date_language=AMERICAN') dt FROM DUAL UNION ALL
  3  SELECT TO_DATE('06/12/2015 13:30:00','MM/DD/YYYY HH24:MI:SS', 'nls_date_language=AMERICAN') dt FROM DUAL UNION ALL
  4  SELECT TO_DATE('06/12/2015 14:00:00','MM/DD/YYYY HH24:MI:SS', 'nls_date_language=AMERICAN') dt FROM DUAL UNION ALL
  5  SELECT TO_DATE('06/12/2015 14:30:00','MM/DD/YYYY HH24:MI:SS', 'nls_date_language=AMERICAN') dt FROM DUAL UNION ALL
  6  SELECT TO_DATE('06/12/2015 15:30:00','MM/DD/YYYY HH24:MI:SS', 'nls_date_language=AMERICAN') dt FROM DUAL
  7  )
  8  SELECT TO_CHAR(dt, 'MM/DD/YYYY HH24', 'nls_date_language=AMERICAN') dt,
  9    COUNT(*)
 10  FROM DATA
 11  GROUP BY TO_CHAR(dt, 'MM/DD/YYYY HH24', 'nls_date_language=AMERICAN')
 12  ORDER BY dt
 13  /

DT              COUNT(*)
------------- ----------
06/12/2015 13          2
06/12/2015 14          2
06/12/2015 15          1

SQL>

Upvotes: 1

Related Questions