Okay, so I have a (SQLite) table called log wherein one column (time) is a Unix epoch timestamp (the other columns are irrelevant for my purposes). These are timestamped log entries.
I am trying to get a count of how many items are logged per hour (all-time -- I don't care to do it only for a given day or whatever; I'm trying to get an idea of activity density throughout the average day). I have been successful in basically binning these into hours with the query SELECT STRFTIME('%H', DATETIME(time, 'unixepoch', 'localtime')) FROM log;
, which then gives me just a huge list of numbers 0-23 which I can run through uniq -c
to get a count of events per hour.
However (more out of stubbornness than any practical need, I admit), I want to do this counting step in SQL. My brain got me as far as knowing I'd prrroooobably have to do some kind of self-join to get only the unique values for the generated hours column, but that's where it crapped out D: Any ideas?