SQLite Forum

SQL query help for between times and grouping times
Richard is correct about the 12, and Keith's solution should work just fine.

This is an alteration to the CTE that should also work. No idea which is better - Perhaps speed test the two and pick the faster one.

The difference here is simply a well-placed modulo 12.

WITH HR(EventDate, EventTime, EventHour) AS (
    SELECT EventDate, EventTime,
           CAST(substr(EventTime,0,instr(EventTime,':')) AS INT) % 12 +
           CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
      FROM trafficdata
SELECT EventDate, EventHour, COUNT(*) AS AccidentsInHour
 GROUP BY EventDate, EventHour