Oracle query, get count of records by hour-CodePudding

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 varchar2! No matter what I try I get either "not a valid month" or "invalid number", depending on the format I use.

The timestamp looks like: 2021-08-08 00:00:52:632

I also executed the following to get NLS format:

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

and get

DD-MON-RRRR.

This is the latest I tried among a dozen others (I commented out the "group by" to just get the darn thing to display).

select to_char(reqts,'mm/dd/yyyy hh24') DATE_HR
--, count(*)
from idcreqresplog
where logdate > trunc(SYSDATE -2)
and logtypeid in (2,4)
--group by to_char(reqts,'mm/dd/yyyy hh24');

Also

select to_char(reqts, 'yyyy-mm-dd hh24:mi:ss.fff' )
--, count(*) 
FROM 
reqresplog 
WHERE 
logdate > trunc(SYSDATE -2) ;
--group by to_date(reqts, 'yyyy-mm-dd HH4');

At my wits end and need some help.

CodePudding user response：

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:

WITH FUNCTION get_timestamp
(
  p_sTimeString VARCHAR2
)
RETURN TIMESTAMP
IS
BEGIN
  RETURN TO_TIMESTAMP(p_sTimeString, 'YYYY-MM-DD HH24:MI:SS.FF3');
EXCEPTION WHEN OTHERS THEN RETURN NULL;
END;
SELECT TO_CHAR(s.hour, 'YYYY-MM-DD HH24') AS HOUR, COUNT(*) AS ROW_COUNT
FROM (SELECT TRUNC(get_timestamp(td.time), 'HH24') AS HOUR,
             td.amount
      FROM test_data td) s
WHERE s.hour IS NOT NULL
GROUP BY s.hour
ORDER BY 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)

CodePudding user response：

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)
GROUP BY
       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 DEFAULT NULL ON 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)
GROUP BY
       TRUNC(
         TO_TIMESTAMP(
           reqts DEFAULT NULL ON CONVERSION ERROR,
           'YYYY-MM-DD HH24:MI:SS:FF'
         ),
         'HH'
       )

db<>fiddle here