SQLite count of order per hour

Question

I only have SQLite on hand, and I want to calculate the count of order per hour per day with table with below columns: orderID, order_datetime(yyyy-mm-dd hh:mm:ss), location

SELECT 
   DATEPART('%H', order_datetime) as 'HOUR',
   COUNT(*) as 'ORDERPERHOUR'
   FROM vanorder
   GROUP BY DATEPART('%H', order_datetime);

I received error 'no such function: DATEPART', how can I achieve this in the world of sqlite? thanks.

Did you search? The documentation is [quite clear](https://sqlite.org/lang_datefunc.html) — trincot, Jul 28 '21 at 11:52
To clarify, do you want the count _for the same hour_ over _any day_, or a break-down per-hour _over all time_? — Dai, Jul 28 '21 at 11:52

Gordon Linoff · Answer 1 · 2021-07-28T22:45:25.560

2

In SQLite, the equivalent uses STRFTIME():

SELECT STRFTIME('%H', order_datetime) as hour
       COUNT(*) as ORDERPERHOUR
FROM vanorder
GROUP BY STRFTIME('%H', order_datetime);

If you are going to use SQLite, you should familiarize yourself with SQLite functions.

edited Jul 28 '21 at 22:45

answered Jul 28 '21 at 11:51

Gordon Linoff

1,242,037
58
646
786

TIL that Sqlite doesn't have a `date`/`datetime` type - [and `STRFTIME` only works if you store dates in an ISO 8601 string](https://stackoverflow.com/questions/63691498/how-to-use-strftime-function-in-sqlite-for-extracting-year-where-datetime-is-i) (_wtf?!_) - so there's nothing automatically stopping `2021-02-31 61:99:61` from being stored. Eeep. – Dai Jul 28 '21 at 11:56
@dai . . . I am not going to defend SQLite's handling of date/time values. But someone using SQLite should definitely be familiar with the functionality in that database. – Gordon Linoff Jul 28 '21 at 11:59
First *familiarize yourself with SQLite functions*. Your code is syntactically and logically wrong. Who upvoted this? – forpas Jul 28 '21 at 22:42

forpas · Accepted Answer · 2021-07-28T13:03:38.350

You can use SQLite's strftime() function with the correct syntax and format string:

SELECT strftime('%Y-%m-%d %H', order_datetime) hour,
       COUNT(*) ORDERPERHOUR
FROM vanorder
GROUP BY hour;

strftime('%Y-%m-%d %H', order_datetime) for a timestamp like '2021-07-28 15:32:12' will return 2021-07-28 15 which is what you want.

If you prefer to see values like '2021-07-28 15:00:00' then change to:

strftime('%Y-%m-%d %H:00:00', order_datetime)

SQLite count of order per hour

2 Answers2