SQLite Forum

SQL query help for between times and grouping times
Login
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        │
└───────────────────────┴──────────┘
```