SQLite Forum

SQL query help for between times and grouping times
Login

SQL query help for between times and grouping times

(1) By ThanksRyan on 2021-04-16 22:24:26 [source]

Hi,

Happy Friday to all you SQLite fans and friends. I'd like help on grouping my EventTime column by the hours.

schema:

CREATE TABLE trafficdata (
    EventID TEXT NOT NULL UNIQUE,
    EventDate TEXT,
    EventTime TEXT,
    EventCode TEXT,
    EventDescription TEXT,
    EventAddress TEXT
);

Sample records:

LLV210400045327,4/10/2021, 10:28:17 PM,401B, ACCIDENT (WITH INJURY),5075 E WASHINGTON AVE
LLV210400045227,4/10/2021, 10:02:08 PM,401, ACCIDENT (TRAFFIC),3667 S LAS VEGAS BLVD
LLV210400045138,4/10/2021, 9:37:40 PM,401C, ACCIDENT (PRIVATE PROPERTY),7614 WESTCLIFF DR
LLV210400045066,4/10/2021, 9:21:49 PM,401A, HIT AND RUN,E TROPICANA AVE / KOVAL LN
LLV210400045017,4/10/2021, 9:09:51 PM,401, ACCIDENT (TRAFFIC),5229 DUNEVILLE ST
LLV210400044913,4/10/2021, 8:47:03 PM,401B, ACCIDENT (WITH INJURY),W DESERT INN RD / S RAINBOW BLVD
LLV210400044827,4/10/2021, 8:26:13 PM,401A, HIT AND RUN,N NELLIS BLVD / E CAREY AVE

Sample plain English question: How many events were there in the 8, 9, and 10 o'clock hours?

8pm,2
9pm,3
10pm,2

This doesn't work:

SELECT EventTime,
       Count(EventTime) AS count
FROM   trafficdata
WHERE  EventTime between '8:%%:%% PM' and '10:%%:%% PM'
GROUP  BY EventTime;  

This doesn't either:

SELECT EventTime,
       Count(EventTime) AS count
FROM   trafficdata
WHERE  EventTime like '8:%%:%% PM'
GROUP  BY EventTime;

What am I doing wrong?

(2) By rayB on 2021-04-16 23:49:46 in reply to 1 [link] [source]

Can I suggest you use timestamps ('YYYY-MM-DD HH:MM:SS') for your date/time events, rather than splitting the date/time values across two columns. Also means you will no longer require the 'AM/PM' suffixes. Please refer to https://www.sqlite.org/lang_datefunc.html

(3) By Keith Medcalf (kmedcalf) on 2021-04-17 01:55:12 in reply to 2 [link] [source]

Using two column to separate the date and the time is OK. Using mixed-endian storage format for the date and the time is not.

(5) By ThanksRyan on 2021-04-17 02:43:13 in reply to 2 [link] [source]

Well it's not my data. I could combine date and time, but I can't easily reformat the date to ISO8601, which is what I'd prefer.

(6) By Keith Medcalf (kmedcalf) on 2021-04-17 03:05:01 in reply to 5 updated by 6.1 [link] [source]

```
create view trafficdatafixed as select *, datetime(substr(EventDate, length(EventDate)-3, 4) || '-' || printf('%02d', substr(EventDate, 1, instr(EventDate, '/') - 1) + 0) || '-' || trim(substr(EventDate, instr(EventDate, '/') + 1, 2), '/') || ' ' || printf('%02d', (substr(EventTime, 1, instr(EventTime,':')-1) + iif(instr(EventTime,'PM') > 1, 12, 0)) % 24) || substr(EventTime,instr(EventTime,':'), 6)) as timestamp from trafficdata;
```

This should work for all common fracked up date and time formats provided that the year is ALWAYS 4 digits and the MINUTES and SECONDS are ALWAYS 2 digits.  (that is, D/M/YYYY for the date and h:mm:ss xM for the time.

(6.1) By Keith Medcalf (kmedcalf) on 2021-04-17 03:13:33 edited from 6.0 in reply to 5 updated by 6.2 [link] [source]

```
create view trafficdatafixed as select *, datetime(substr(EventDate, length(EventDate)-3, 4) || '-' || printf('%02d', substr(EventDate, 1, instr(EventDate, '/') - 1) + 0) || '-' || trim(substr(EventDate, instr(EventDate, '/') + 1, 2), '/') || ' ' || printf('%02d', (substr(EventTime, 1, instr(EventTime,':')-1) + iif(instr(EventTime,'PM') > 1, 12, 0)) % 24) || substr(EventTime,instr(EventTime,':'), 6)) as timestamp from trafficdata;
```

This should work for all common fracked up date and time formats provided that the year is ALWAYS 4 digits and the MINUTES and SECONDS are ALWAYS 2 digits.  (that is, M/D/YYYY for the date and h:mm:ss xM for the time.

(6.2) By Keith Medcalf (kmedcalf) on 2021-04-17 03:17:10 edited from 6.1 in reply to 5 [link] [source]

create view trafficdatafixed as select *, datetime(substr(EventDate, length(EventDate)-3, 4) || '-' || printf('%02d', substr(EventDate, 1, instr(EventDate, '/') - 1) + 0) || '-' || trim(substr(EventDate, instr(EventDate, '/') + 1, 2), '/') || ' ' || printf('%02d', (substr(EventTime, 1, instr(EventTime,':')-1) + iif(instr(EventTime,'PM') > 1, 12, 0)) % 24) || substr(EventTime,instr(EventTime,':'), 6)) as timestamp from trafficdata;

This should work for all common fracked up date and time formats provided that the year is ALWAYS 4 digits and the MINUTES and SECONDS are ALWAYS 2 digits. (that is, M/D/YYYY for the date and h:mm:ss xM for the time.

Ooops. If the data format allows single digit days then you need this:

create view trafficdatafixed as select *, datetime(substr(EventDate, length(EventDate)-3, 4) || '-' || printf('%02d', substr(EventDate, 1, instr(EventDate, '/') - 1) + 0) || '-' || printf('%02d', trim(substr(EventDate, instr(EventDate, '/') + 1, 2), '/') + 0) || ' ' || printf('%02d', (substr(EventTime, 1, instr(EventTime,':')-1) + iif(instr(EventTime,'PM') > 1, 12, 0)) % 24) || substr(EventTime,instr(EventTime,':'), 6)) as timestamp from trafficdata;

(8) By Keith Medcalf (kmedcalf) on 2021-04-17 03:10:14 in reply to 6.0 [link] [source]

sqlite> select * from trafficdatafixed;
┌─────────────────┬───────────┬─────────────┬───────────┬─────────────────────────────┬──────────────────────────────────┬───────────────────────────┐
│     EventID     │ EventDate │  EventTime  │ EventCode │      EventDescription       │           EventAddress           │         timestamp         │
├─────────────────┼───────────┼─────────────┼───────────┼─────────────────────────────┼──────────────────────────────────┼───────────────────────────┤
│ LLV210400045327 │ 4/10/2021 │ 10:28:17 PM │ 401B      │ ACCIDENT (WITH INJURY)      │ 5075 E WASHINGTON AVE            │ 2021-04-10 22:28:17.000 Z │
│ LLV210400045227 │ 4/10/2021 │ 10:02:08 PM │ 401       │ ACCIDENT (TRAFFIC)          │ 3667 S LAS VEGAS BLVD            │ 2021-04-10 22:02:08.000 Z │
│ LLV210400045138 │ 4/10/2021 │ 9:37:40 PM  │ 401C      │ ACCIDENT (PRIVATE PROPERTY) │ 7614 WESTCLIFF DR                │ 2021-04-10 21:37:40.000 Z │
│ LLV210400045066 │ 4/10/2021 │ 9:21:49 PM  │ 401A      │ HIT AND RUN                 │ E TROPICANA AVE / KOVAL LN       │ 2021-04-10 21:21:49.000 Z │
│ LLV210400045017 │ 4/10/2021 │ 9:09:51 PM  │ 401       │ ACCIDENT (TRAFFIC)          │ 5229 DUNEVILLE ST                │ 2021-04-10 21:09:51.000 Z │
│ LLV210400044913 │ 4/10/2021 │ 8:47:03 PM  │ 401B      │ ACCIDENT (WITH INJURY)      │ W DESERT INN RD / S RAINBOW BLVD │ 2021-04-10 20:47:03.000 Z │
│ LLV210400044827 │ 4/10/2021 │ 8:26:13 PM  │ 401A      │ HIT AND RUN                 │ N NELLIS BLVD / E CAREY AVE      │ 2021-04-10 20:26:13.000 Z │
└─────────────────┴───────────┴─────────────┴───────────┴─────────────────────────────┴──────────────────────────────────┴───────────────────────────┘

Note that your timestamp will not contain milliseconds and will be naive.

(9) By Keith Medcalf (kmedcalf) on 2021-04-17 08:23:44 in reply to 6.2 [link] [source]

I think that is still wrong -- this works for the 12 AM and 12 PM:

select *,
       printf('%4d-%02d-%02d %02d:%s',
            substr(EventDate, length(EventDate)-3, 4) + 0,
            substr(EventDate, 1, instr(EventDate, '/') - 1) + 0,
            trim(substr(EventDate, instr(EventDate, '/') + 1, 2), '/') + 0,
            substr(EventTime, 1, instr(EventTime, ':') - 1) + iif(instr(EventTime, 'PM') > 1 and substr(EventDate,1,2) != '12', 12, iif(instr(EventTime, 'AM') > 1 and substr(EventTime, 1, 2) == '12', -12, 0)),
            substr(EventTime, instr(EventTime, ':') + 1, 6)) as timestamp
  from trafficdata;

(10) By Keith Medcalf (kmedcalf) on 2021-04-17 09:41:24 in reply to 6.2 [link] [source]

Here is some SQL that will validate the conversion.

with src1(x)
  as (
         select strftime('%Y-%m-%d %H:%M:%S', 'now')
      union all
         select strftime('%Y-%m-%d %H:%M:%S', x, '+1 hour')
           from src1
          limit 8760
     ),
     src2(x, dp, th, trest)
  as (
      select x,
             printf('%d/%d/%d', strftime('%m', x) + 0, strftime('%d', x) + 0, strftime('%Y', x) + 0),
             strftime('%H', x),
             strftime(':%M:%S', x)
        from src1
     ),
     src3(x, dp, th, trest, nh, ap)
  as (
      select x,
             dp,
             th,
             trest,
             th + iif(th between '13' and '23', -12, iif(th == '00', 12, 0)),
             iif(th between '12' and '23', 'PM', 'AM')
        from src2
     ),
     src4(x, dp, tp)
  as (
      select x,
             dp,
             nh || trest || ' ' || ap
       from src3
     ),
     cvt(x, dp, tp, y)
  as (
      select x,
             dp,
             tp,
             printf('%4d-%02d-%02d %02d:%s',
                  substr(dp, length(dp)-3, 4) + 0,
                  substr(dp, 1, instr(dp, '/') - 1) + 0,
                  trim(substr(dp, instr(dp, '/') + 1, 2), '/') + 0,
                  substr(tp, 1, instr(tp, ':') - 1) + iif(instr(tp, 'PM') > 1 and substr(tp,1,2) != '12', 12, iif(instr(tp, 'AM') > 1 and substr(tp, 1, 2) == '12', -12, 0)),
                  substr(tp, instr(tp, ':') + 1, 5))
        from src4
     )
select *, x == y
  from cvt
;

(12) By ThanksRyan on 2021-04-17 16:01:23 in reply to 10 [link] [source]

Dang, Keith! This is absolutely amazing. Thank you for your time in this. Now it'll take me a lifetime to figure out what the heck it's doing.

Maybe I can kindly ask the city to covert to using ISO8601 date & time stamps. :)

(19) By Tim Streater (Clothears) on 2021-04-18 08:13:25 in reply to 12 [link] [source]

Personally I'd be asking them to convert to storing date and time in one column, the number of seconds since the epoch. That way you properly separate data storage from data presentation and comparisons then become trivial and comprehensible.

(20) By Keith Medcalf (kmedcalf) on 2021-04-18 19:08:15 in reply to 12 [link] [source]

It is stepwise.

src1 contains timestamps in the IOS8601 format recognized by SQLite3 (YYYY-MM-DD HH:MM:SS) for 8760 hours from now every hour for one year. This will include all possible permutations of formatting when reconstructed into 12-hour format.

src2 computes the datepart (dp) for the timestamp in the form m/d/y with no padding, and the 24-hour part of the timestamp (th), plus the rest of the timestamp (:mm:ss) trest.

src3 uses the 24-hour number and computes the 12-hour value (nh) and the AM/PM indicator (ap).

src4 glues together the 12-hour value, the rest of the timestring, and the AM/PM indicator to form a 12-hour time string.

cvt contains the original ISO8601 timestamp, the datepart (dp) computed above, the 12-hour timepart (tp), and a new ISO8601 timestamp computed using the dp and tp.

The end select displays all the data plus an indication of whether the timestamp reconstructed from the deconstructed timestamps is the same as the original timestamp.

(21) By ThanksRyan on 2021-04-18 21:25:15 in reply to 20 [link] [source]

Thank you for your help explaining what you created and how it works. Your contributions on this forum (and previously the mailing list) don't go unnoticed by the members and myself. Your contributions are outstanding.

Keep up the excellent work.

A couple people have mentioned it would be better if the date & time were combined into one column. Do you think that's generally good advice if the source isn't in ISO8601? What advantages would there be?

As it is now, I can find all events based just on EventDate without a worry about the timestamp:

sqlite> select EventDate, count(*) from trafficdata group by EventDate;
┌───────────┬──────────┐
│ EventDate │ count(*) │
├───────────┼──────────┤
│ 4/10/2021 │ 13       │
│ 4/11/2021 │ 86       │
│ 4/12/2021 │ 113      │
│ 4/13/2021 │ 135      │
│ 4/14/2021 │ 112      │
│ 4/15/2021 │ 111      │
│ 4/16/2021 │ 126      │
│ 4/17/2021 │ 103      │
│ 4/18/2021 │ 30       │
└───────────┴──────────┘

If they're combined, the same query is useless:

sqlite> select EventDate, count(*) from trafficdata group by EventDate limit 5;
┌───────────────────────┬──────────┐
│       EventDate       │ count(*) │
├───────────────────────┼──────────┤
│ 4/10/2021 10:02:08 PM │ 1        │
│ 4/10/2021 10:28:17 PM │ 1        │
│ 4/10/2021 10:56:22 PM │ 1        │
│ 4/10/2021 11:00:00 PM │ 1        │
│ 4/10/2021 11:58:33 PM │ 1        │
└───────────────────────┴──────────┘

(23) By Larry Brasfield (larrybr) on 2021-04-18 22:07:14 in reply to 21 [link] [source]

As it is now, I can find all events based just on EventDate without a worry about the timestamp:As it is now, I can find all events based just on EventDate without a worry about the timestamp:

...

If they're combined, the same query is useless:

...

Certainly, if you care only to resolve dates, and times are an entirely separate concern, that separation is not causing too much trouble. But when your events have a duration, perhaps represented with an event_begin and event_end datetime, the ISO 8601 representation's merit will become more apparent. Programmers like it because its lexical ordering and its time ordering are the same.

Suppose that your EventDate becomes EventDateTime, using ISO 8601: ┌──────────────────┐ │ EventDateTime │ ├──────────────────┤ │ 2021-04-10T13:30 │ │ 2021-04-11T04:15 │ │ 2021-04-11T05:45 │ │ ... │ └──────────────────┘ . Now you want a query which counts events by day, dropping the time (ignoring the ambiguity of "day" going around the world.) A GROUP BY substring(EventDateDtime, 1, 10) clause gets that. Seems like extra trouble for just that usage.

Suppose you want events falling within a date range. A WHERE EventDateDtime BETWEEN 2021-04-10T00:00 AND 2021-04-11T23:59 clause would capture the above 3 datetimes. This is very little extra trouble.

Now imagine you are writing a query to look for scheduled timeslot gaps in an event location which is open through midnight. Then, you will find that having a simply ordered time representation pays off.

There is a reason that ISO 8601 has become beloved by programmers. Among the textual time representations, it has no peer.

(24) By Ryan Smith (cuz) on 2021-04-19 02:01:02 in reply to 21 [link] [source]

I agree with Larry, and to further emphasize: It's not the one-columness that is important, it is the single correct time-value.

There's very little point to pushing non ISO8601 dates and times together. The advantage only surfaces if you can deduce from that a proper date-time value such as ISO8601 or even a Julianday or GMT seconds or such, so that you have one field that gives a specific point in time with which you can calculate precedence, duration, order, containment and the like and format it easily with the existing time-format functions into whatever output you or your users may fancy.

(4) By Ryan Smith (cuz) on 2021-04-17 02:04:07 in reply to 1 [link] [source]

First make a sub-query or CTE that correctly parse out the physical hour, then run the GROUP BY on that query.

If you only want counts per hour, we only need the date and hour as fields in the CTE, which is rather trivial - I did this quick Example:


CREATE TABLE trafficdata (
    EventID TEXT NOT NULL UNIQUE,
    EventDate TEXT,
    EventTime TEXT,
    EventCode TEXT,
    EventDescription TEXT,
    EventAddress TEXT
);

INSERT INTO trafficdata (EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES
 ('LLV210400045327','4/10/2021', '10:28:17 PM','401B', 'ACCIDENT (WITH INJURY)','5075 E WASHINGTON AVE')
,('LLV210400045227','4/10/2021', '10:02:08 PM','401',  'ACCIDENT (TRAFFIC)','3667 S LAS VEGAS BLVD')
,('LLV210400045138','4/10/2021', '9:37:40 PM', '401C', 'ACCIDENT (PRIVATE PROPERTY)','7614 WESTCLIFF DR')
,('LLV210400045066','4/10/2021', '9:21:49 PM', '401A', 'HIT AND RUN','E TROPICANA AVE / KOVAL LN')
,('LLV210400045017','4/10/2021', '9:09:51 PM', '401',  'ACCIDENT (TRAFFIC)','5229 DUNEVILLE ST')
,('LLV210400044913','4/10/2021', '8:47:03 PM', '401B', 'ACCIDENT (WITH INJURY)','W DESERT INN RD / S RAINBOW BLVD')
,('LLV210400044827','4/10/2021', '8:26:13 PM', '401A', 'HIT AND RUN','N NELLIS BLVD / E CAREY AVE')
;

  -- Check to see that we have added the data correctly:
SELECT EventDate, EventTime
  FROM trafficdata;

  -- EventDate  |EventTime    
  -- -----------|-------------
  -- 4/10/2021  |10:28:17 PM  
  -- 4/10/2021  |10:02:08 PM  
  -- 4/10/2021  |9:37:40 PM   
  -- 4/10/2021  |9:21:49 PM   
  -- 4/10/2021  |9:09:51 PM   
  -- 4/10/2021  |8:47:03 PM   
  -- 4/10/2021  |8:26:13 PM   



-- Make the CTE and GROUP BY the hour to see counts per hour:
WITH HR(EventDate, EventTime, EventHour) AS (
    SELECT EventDate, EventTime,
           CAST(substr(EventTime,0,instr(EventTime,':')) AS INT)+
           CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END
      FROM trafficdata
)
SELECT EventDate, EventHour, COUNT(*) AS AccidentsInHour
  FROM HR
 GROUP BY EventDate, EventHour
;

  --            |            |Accident-
  -- EventDate  |  EventHour | sInHour 
  -- -----------|------------|---------
  -- 4/10/2021  |     20     |    2    
  -- 4/10/2021  |     21     |    3    
  -- 4/10/2021  |     22     |    2    

Hope that helps!

PS: This data sadly cannot be sorted because of those bad date & time formats. You should really use proper ISO8601 format (or at least a sub-format of ISO8601 that SQLite can work with - i.e. YYYY-MM-DD HH:NN:SS).

(11) By ThanksRyan on 2021-04-17 15:59:53 in reply to 4 [link] [source]

I THINK everything is right with this CTE query.

Example data here: https://termbin.com/9y2s

Now for the results:

┌───────────┬───────────┬─────────────────┐
│ EventDate │ EventHour │ AccidentsInHour │
├───────────┼───────────┼─────────────────┤
│ 4/16/2021 │ 2         │ 1               │
│ 4/16/2021 │ 3         │ 2               │
│ 4/16/2021 │ 5         │ 4               │
│ 4/16/2021 │ 6         │ 2               │
│ 4/16/2021 │ 7         │ 4               │
│ 4/16/2021 │ 8         │ 7               │
│ 4/16/2021 │ 9         │ 2               │
│ 4/16/2021 │ 10        │ 5               │
│ 4/16/2021 │ 11        │ 5               │
│ 4/16/2021 │ 12        │ 1               │
│ 4/16/2021 │ 13        │ 14              │
│ 4/16/2021 │ 14        │ 5               │
│ 4/16/2021 │ 15        │ 11              │
│ 4/16/2021 │ 16        │ 12              │
│ 4/16/2021 │ 17        │ 5               │
│ 4/16/2021 │ 18        │ 8               │
│ 4/16/2021 │ 19        │ 4               │
│ 4/16/2021 │ 20        │ 6               │
│ 4/16/2021 │ 21        │ 5               │
│ 4/16/2021 │ 22        │ 6               │
│ 4/16/2021 │ 23        │ 4               │
│ 4/16/2021 │ 24        │ 12              │
└───────────┴───────────┴─────────────────┘

24 seems to represent 12nooon. Can you explain which part of the substitution does that?

These CTE things are really, really cool and powerful!

(14) By Richard Damon (RichardDamon) on 2021-04-17 23:23:21 in reply to 11 [link] [source]

My guess is that the code isn't handling that 12 comes before 1, so should really be changed to 0.

(15) By Keith Medcalf (kmedcalf) on 2021-04-17 23:43:01 in reply to 11 [link] [source]

This fragment correctly calculates the 24-hour hour offset from the 'h:mm:ss aM' string in tp. The problem is that 12 AM and 12 PM must be handled specially.

substr(tp, 1, instr(tp, ':') - 1) + iif(instr(tp, 'PM') > 1 and substr(tp,1,2) != '12', 12, iif(instr(tp, 'AM') > 1 and substr(tp, 1, 2) == '12', -12, 0))

You can also use

trim(substr(tp, 1, 2), ':') + iif(instr(tp, 'PM') > 1 and substr(tp,1,2) != '12', 12, iif(instr(tp, 'AM') > 1 and substr(tp, 1, 2) == '12', -12, 0))

This is, if the time is 'PM' and the hour is NOT 12, then add 12 to the hour, else if the time is 'AM' and the hour is 12 then subtract 12 from the hour, otherwise leave the hour alone (it is between 1 and 11 in the AM and does not need adjustment).

(16) By Ryan Smith (cuz) on 2021-04-18 01:23:52 in reply to 11 [link] [source]

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
  FROM HR
 GROUP BY EventDate, EventHour
;

(18) By Keith Medcalf (kmedcalf) on 2021-04-18 07:22:00 in reply to 16 [link] [source]

The mod 12 is faster:

trim(substr(EventTime, 1, 2), ':') % 12 + iif(instr(EventTime, 'PM') > 1, 12, 0)

(17) By Ryan Smith (cuz) on 2021-04-18 01:53:01 in reply to 11 [link] [source]

24 seems to represent 12nooon. Can you explain which part of the substitution does that?

The important bit of that CTE is this select:

    SELECT EventDate, EventTime,
           CAST(substr(EventTime,0,instr(EventTime,':')) AS INT)+
           CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END

of which the third field is this:

CAST(substr(EventTime,0,instr(EventTime,':')) AS INT) + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END

To unpack it I will start from the inside and use this time as the example: " 8:12:34 PM"

This section: ......, instr(EventTime,':').....
Returns the index of the first colon character in EventTime. So for our example time, that will be 2 (Since it is Zero-based, so at position Zero there is a space, at position 1 there is an 8 and at position 2 there is the colon, which is returned). This makes the statement resolve to:

CAST(substr(EventTime,0, 2 ) AS INT) + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END

This section: ......substr(EventTime,0, 2 ).....
Returns the part of the string starting at character Zero and includes the next 2 characters (i.e. the number of characters to include is deduced previously from the index of the colon). Note that this also elegantly handles the problem of the leading space. This makes the statement resolve to:

CAST(' 8' AS INT) + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END

The CAST simply changes ' 8' to the Integer value 8. This makes the statement:

8 + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END

The CASE checks if the EventTime ends with a PM, in which case it adds 12, otherwise it adds nothing (0). Since our example does end with a PM, it resolves to this:

8 + 12

which returns the hour correctly as: 20

The problem that arose then was that 12 AM resolved by the same steps to 12 + 0 (which is 12 and is wrong, it should be Zero-hour/midnight) and 12 PM resolved to 12 + 12 which is 24 - wrong again, it should be 12.

So to fix that, we need a modulo 12 in the statement because 12 % 12 = 0, which means 12 AM resolves to 12 % 12 + 0 which gives 0 correctly, and 12 PM resolves to 12 % 12 + 12 which gives 12 correctly.

Final statement thus becomes:

CAST(substr(EventTime,0,instr(EventTime,':')) AS INT) % 12 + CASE WHEN EventTime LIKE '%PM' THEN 12 ELSE 0 END

Hope that answers the question!

(22) By ThanksRyan on 2021-04-18 21:35:27 in reply to 17 [link] [source]

Hi Ryan,

Can you see why I made my username "ThanksRyan"? Your ability to, not only craft the CTE, but explain it with the detail you did is truly appreciative. This community, because of the kind of person drh and his team are, fosters humble, helpful, talented folks.

Thank you for being a member here and patiently contributing to help people better understand SQL and SQLite.

(25) By Ryan Smith (cuz) on 2021-04-19 02:21:25 in reply to 22 [link] [source]

Can you see why I made my username "ThanksRyan"?

I wasn't sure what that moniker tried to convey before, and now I'm quite humbled but also a little uncomfortable with it, while still appreciating the sentiment.

I know I have no right to ask, but You'd do me a great honor by changing that to your real name or preferred handle, I'd much sooner enjoy talking to a person than an accolade.

As for the thanks for us posting here - you're welcome. I'm sure I speak for most here[1] when I say that posting is a pleasure, and that I have gained a hundred-fold more knowledge from this forum than I ever imparted on it.

[1] Except for Igor Tandetnik. He only ever imparted knowledge. :)

(7) By John Dennis (jdennis) on 2021-04-17 03:09:11 in reply to 1 [link] [source]

>This doesn't work:

But this one does although possibly not in way you would expect. Your time string has a space preceding the 8. Include that space and rows are returned. 

SELECT EventTime,
       Count(EventTime) AS count
FROM   trafficdata
WHERE  EventTime between ' 8:%%:%% PM' and '10:%%:%% PM'
GROUP  BY EventTime;

The preceding comments about using proper date and time formats are a much preferred solution

(13) By ThanksRyan on 2021-04-17 16:03:06 in reply to 7 [link] [source]

this one does although possibly not in way you would expect.

Yeah, you're right. It'll tell me no two unique calls shared the same exact EventTime, but that's pretty useless.

Your time string has a space preceding the 8

Very good eye there. Thanks for the input.