Group Date column based on hours

Question

I have a table in sqlite database where I store data about call logs. As an example assume that my table looks like this

| Calls_count | Calls_duration | Time_slice | Time_stamp |
|    10       |     500        | 21         | 1399369269 |
|     2       |     88         | 22         | 1399383668 |

Here

Calls_count is calls made since last observations

Calls_duration is the duration of calls in ms since last observations

Time-slice represents a time portion of week. Every day is divided into 4 portions of 6 hours each such that

     06:00-11:59 | 12:00-17:59 | 18:00- 23.59 | 24:00-05:59 |
Mon| 11          | 12          | 13           | 14          | 
Tue| 21          | 22          | 23           | 24          | 
Wed| 31          | 32          | 33           | 34          | 
Thu| 41          | 42          | 43           | 44          | 
Fri| 51          | 52          | 53           | 54          | 
Sat| 61          | 62          | 63           | 64          | 
Sun| 71          | 72          | 73           | 74          |

And the time_stamp is unix epoch when the observation was made/ record was inserted in the database

Now I want to create a query so that if I specify time_stamp for a start and the end of week, The result is 168 rows of data, giving me sum of calls grouped by hour such that I get 24 rows for each day of week. This is an hourly break down of calls in a week.

SUM_CALLS | Time_Slice | Hour_of_Week |
10        | 11         | 1            |  
0         | 11         | 2            | 
....
7         | 74         | 167          |  
4         | 74         | 168          |

In the above example of intended result,

    1st row is  Monday 06:00-06:59 
    2nd row is  Monday 07:00-07:59
    Last row is Sunday 04:00-05:59

josepn · Accepted Answer · 2014-05-08T14:36:37.267

Since version 3.8.3 SQLite supports common table expressions and this is a possible solution

WITH RECURSIVE
  hours(x,y) AS (SELECT CAST(STRFTIME('%s',STRFTIME('%Y-%m-%d %H:00:00', '2014-05-05 00:00:00')) AS INTEGER),
                        CAST(STRFTIME('%s',STRFTIME('%Y-%m-%d %H:59:59', '2014-05-05 00:00:00')) AS INTEGER)

            UNION ALL

                SELECT x+3600,y+3600 FROM hours LIMIT 168)

SELECT
        COALESCE(SUM(Calls_count),0) AS SUM_CALLS,
        CASE CAST(STRFTIME('%w',x,'unixepoch') AS INTEGER)
          WHEN 0 THEN 7 ELSE STRFTIME('%w',x,'unixepoch') END
        ||
        CASE
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '06:00:00' AND '11:59:59' THEN 1
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '12:00:00' AND '17:59:59' THEN 2
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '18:00:00' AND '23:59:59' THEN 3
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '00:00:00' AND '05:59:59' THEN 4
        END AS Time_Slice,
        ((x-(SELECT MIN(x) FROM hours))/3600)+1 AS Hour_of_Week

  FROM hours LEFT JOIN call_logs
    ON call_logs.time_stamp >= hours.x AND call_logs.time_stamp <= hours.y
    GROUP BY Hour_of_Week
    ORDER BY Hour_of_Week
    ;

This is tested with SQLite version 3.7.13 without cte:

DROP VIEW IF EXISTS digit;
CREATE TEMPORARY VIEW digit AS SELECT 0 AS d UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
                              SELECT 5      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
                              ;

DROP VIEW IF EXISTS hours;
CREATE TEMPORARY VIEW hours AS SELECT STRFTIME('%s','2014-05-05 00:00:00') + s AS x,
                                      STRFTIME('%s','2014-05-05 00:00:00') + s+3599  AS y
                                FROM (SELECT (a.d || b.d || c.d) * 3600 AS s FROM digit a, digit b, digit c LIMIT 168)
                                ;

SELECT
        COALESCE(SUM(Calls_count),0) AS SUM_CALLS,
        CASE CAST(STRFTIME('%w',x,'unixepoch') AS INTEGER)
          WHEN 0 THEN 7 ELSE STRFTIME('%w',x,'unixepoch') END
        ||
        CASE
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '06:00:00' AND '11:59:59' THEN 1
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '12:00:00' AND '17:59:59' THEN 2
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '18:00:00' AND '23:59:59' THEN 3
          WHEN STRFTIME('%H:%M:%S',x,'unixepoch') BETWEEN '00:00:00' AND '05:59:59' THEN 4
        END AS Time_Slice,
        ((x-(SELECT MIN(x) FROM hours))/3600)+1 AS Hour_of_Week

  FROM hours LEFT JOIN call_logs
    ON call_logs.time_stamp >= hours.x AND call_logs.time_stamp <= hours.y
    GROUP BY Hour_of_Week
    ORDER BY Hour_of_Week
    ;

Unfortunately I can't test this with my sqlite database as it came from Android device, which has SQLite 3.7.11 even in its latest OS version 4.4 (Kit Kat) according to [Mark Carter's post](http://stackoverflow.com/a/4377116/777593). Is there any way to convert my old SQLite database from 3.7.x to 3.8.3? — Khurram Majeed, May 08 '14 at 11:54
If you can not use new versions of SQLite can try without cte. I added a new version. — josepn, May 08 '14 at 14:42

Group Date column based on hours

1 Answers1