SQLITE query hourly data over a date range

There was a request to get monthly summary data in a half hourly period. This is to allow user to view the month's data by the half hour range. For example, user need to know what is the volume of customer coming in at which period so that they could prepare to have enough staff to cater to the inflow.

The following is assuming the date is stored as UnixTimeStamp.

The first task is to get the half hour period using strftime.

strftime('%H',DateCreated,'unixepoch','localtime') as Hour, case when cast(strftime('%M',DateCreated,'unixepoch','localtime') as integer) <30 as="" else="" end="" minute="" p="" then="">
Don't forget to get the count

,count(Customer) as Count

Of course you must define the table

from Customer

Next thing to set is the date range as month.

where strftime('%Y-%m-%d',DateCreated,'unixepoch') between '2018-08-01' and '2018-09-01'

We also want to limit the time to 10am-10pm

and strftime('%H:%M:%s',DateCreated,'unixepoch','localtime') between '10:00:00' and '23:00:00'

Finally group by the hour and minute

group by Hour, Minute

The result is you get 3 columns Hour, Minute, Count like

10 00 5
10 30 1
11 00 95
,..

Obviously, at a certain time, there will be no data thus the specific time period will be missing. A good way is to create an associative array with all the time defined and the value of Count set as 0. Use Hour, Minute as the key like '1000'. Update the count using the keys from the result. In this way a complete list of the period will be available.