SELECT most common HOUR in COLUMN in SQL-CodePudding

I am trying to select the most common hour a service has been requested.

I currently have a column

CALLOUT_TIME
14:12
12:55
13:18
17:55
14:00
14:45
19:00
05:20

And I want to pull out the most called upon hour in this case (14:00, 14 or 2pm).

I was hoping to group by Hour, or at least have a a count in desc order to highlight the most called upon.

The column is currently set at VARCHAR(2)

SELECT SUBSTR(CALLOUT_TIME, 1, 2) AS HH FROM REQUESTS;

Which will bring me:

HH
14
12
13
17
14
14
19
05 etc..

I was hoping to group by Hour, or at least have a a count in desc order to highlight the most called upon.

There is no DATETIME data type option and I cannot set as TIMESTAMP. Is there a reason I have no DATETIME option, am I using the wrong version of SQL Developer?

CodePudding user response：

You are using VARCHAR2 for storing the time, because in Oracle there exists no TIME datatype. This is okay. (You could use their datetime datatype that they inappropriately call DATE, but then you'd have to use a dummy date along. The only advantage would be that you'd have a built-in check for valid times.)

You know how to get the hour and you say you want to group by hour and sort by count. So, do this.

SELECT SUBSTR(callout_time, 1, 2) AS hh
FROM REQUESTS
GROUP BY SUBSTR(callout_time, 1, 2)
ORDER BY COUNT(*) DESC
FETCH FIRST ROW WITH TIES;