There is a table (oracle) of requests with date / time type

| id| req_time | |---|------------------------------| | 1 | 02.07.16 10:04:43,984000000 | | 2 | 02.07.16 10:04:42,397000000 | | 3 | 02.07.16 10:04:39,718000000 |

How to calculate the number of rows (queries) by hours during a period (for example, a week)?

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

1 answer 1

 select trunc(req_time,'HH'), count(1) from table where req_time between TO_DATE('01072016','DDMMYYYY') and TO_DATE('07072016','DDMMYYYY')+0.9999942 group by trunc(req_time,'HH')

Actually the condition in where selects the period you want, and trunc truncates the date to hours. Constant 0.9999942 to approximate the time in the bottom to 23: 59: 59.9.

  • Thank. It seems to be true, but there is one question left: I added a sort: <pre> select trunc (created, 'HH'), count (1) from ',' DDMMYYYY ') + 0.9999942 group by trunc (created,' HH ') order by trunc (created,' HH '); </ pre> And it turned out like this: <pre> | trunc (created, 'HH') | count (1) | | ------------------------- | ------------ | | 07/01/16 | 37 | | 07/01/16 | 46 | </ pre> How to make it still visible time (what exactly hour)? - Dmitry Roo
  • @DmitryRoo Time must be visible. at least the datetime field is returned there. Maybe your default client part just takes a date from it. You can try converting it to select TO_CHAR(trunc(created,'HH'),'DD/MM/YYYY HH24'), ... - Mike