Oracle Query, Get Count Of Records By Hour

I am trying to get transaction counts for every hour. Normally it is a straight forward query by unfortunately the timestamp column I have to work with is not timestamp but varchar

Solution 1:

Assuming that your column is always in the format 2021-08-08 00:00:52:63 then group on the substring up to the 13th character:

SELECT SUBSTR(reqts, 1, 13) AS date_hr,
       count(*)
FROM   idcreqresplog
WHERE  logdate > trunc(SYSDATE -2)
AND    logtypeid in (2,4)
GROUPBY
       SUBSTR(reqts, 1, 13);

If you do want to convert to a date then, from Oracle 12.2, you can use TO_TIMESTAMP(string_value DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD HH24:MI:SS:FF'):

SELECT TRUNC(
         TO_TIMESTAMP(
           reqts DEFAULTNULLON CONVERSION ERROR,
           'YYYY-MM-DD HH24:MI:SS:FF'
         ),
         'HH'
       ) AS date_hr,
       COUNT(*)
FROM   idcreqresplog
WHERE  logdate > trunc(SYSDATE -2)
AND    logtypeid in (2,4)
GROUPBY
       TRUNC(
         TO_TIMESTAMP(
           reqts DEFAULTNULLON CONVERSION ERROR,
           'YYYY-MM-DD HH24:MI:SS:FF'
         ),
         'HH'
       )

db<>fiddle here

Solution 2:

Assuming as LittleFoot suggested, that some of your data is bad, you can use an inline WITH function to root out your bad data. Take the following example:

WITHFUNCTION get_timestamp
(
  p_sTimeString VARCHAR2
)
RETURNTIMESTAMPISBEGINRETURN TO_TIMESTAMP(p_sTimeString, 'YYYY-MM-DD HH24:MI:SS.FF3');
EXCEPTION WHEN OTHERS THENRETURNNULL;
END;
SELECT TO_CHAR(s.hour, 'YYYY-MM-DD HH24') ASHOUR, COUNT(*) AS ROW_COUNT
FROM (SELECT TRUNC(get_timestamp(td.time), 'HH24') ASHOUR,
             td.amount
      FROM test_data td) s
WHERE s.hour ISNOTNULLGROUPBY s.hour
ORDERBY s.hour;

Here is the DBFiddle showing this working for some good and bad data (Link).

What the query does is use an inline function to call the TO_TIMESTAMP function. Then it just catches any error and returns NULL. This saves you from your bad data messing up your query. After that, the query is pretty much as you had tried earlier. I truncate the timestamp to the hour in the inner query and then use that to group by in the outer query (Only using the rows which don't have NULL timestamps...meaning they didn't error)