Search code examples
sqloracle12cdate-format

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 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.


Solution

  • 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