[postgres@sofa ~]$ sql_tdwtf topics_by_hours SELECT DATE_TRUNC('day', created_at), EXTRACT(hour FROM created_at), topic_id, count(id) FROM posts WHERE created_at between '01/01/14' and '01/01/19' GROUP BY DATE_TRUNC('day', created_at), EXTRACT(hour FROM created_at), topic_id HAVING count(id)>50 ORDER BY DATE_TRUNC('day', created_at) ASC, EXTRACT(hour FROM created_at) ASC date_trunc | date_part | topic_id | count ---------------------+-----------+----------+------- 2014-05-23 00:00:00 | 0 | 344 | 61 2014-06-13 00:00:00 | 19 | 759 | 66 2014-06-19 00:00:00 | 14 | 1000 | 65 2014-06-19 00:00:00 | 15 | 1000 | 52 2014-06-19 00:00:00 | 20 | 1008 | 64 2014-06-20 00:00:00 | 15 | 1000 | 97 2014-06-20 00:00:00 | 17 | 1000 | 130 2014-06-20 00:00:00 | 20 | 1000 | 76 2014-06-27 00:00:00 | 21 | 1000 | 55 2014-06-30 00:00:00 | 22 | 1000 | 61 2014-07-01 00:00:00 | 18 | 1000 | 57 2014-07-15 00:00:00 | 19 | 1000 | 87 2014-07-15 00:00:00 | 21 | 1000 | 236 2014-07-15 00:00:00 | 22 | 1000 | 206 2014-07-16 00:00:00 | 19 | 1593 | 53 2014-07-16 00:00:00 | 22 | 1596 | 61 2014-07-17 00:00:00 | 14 | 1000 | 59 2014-07-18 00:00:00 | 19 | 1000 | 79 2014-07-18 00:00:00 | 20 | 1000 | 152 2014-07-19 00:00:00 | 2 | 1000 | 103 2014-07-19 00:00:00 | 3 | 1000 | 67 2014-07-21 00:00:00 | 15 | 1000 | 93 2014-07-21 00:00:00 | 16 | 1000 | 56 2014-07-21 00:00:00 | 17 | 1000 | 75 2014-07-21 00:00:00 | 18 | 1000 | 67 2014-07-21 00:00:00 | 19 | 1000 | 128 2014-07-21 00:00:00 | 20 | 1000 | 115 2014-07-22 00:00:00 | 14 | 1000 | 75 2014-07-22 00:00:00 | 15 | 1000 | 55 2014-07-22 00:00:00 | 17 | 1000 | 72 2014-07-22 00:00:00 | 18 | 1000 | 60 2014-07-22 00:00:00 | 19 | 1000 | 132 2014-07-22 00:00:00 | 20 | 1000 | 85 2014-07-22 00:00:00 | 21 | 1000 | 72 2014-07-23 00:00:00 | 9 | 1809 | 58 2014-07-23 00:00:00 | 10 | 1000 | 63 2014-07-23 00:00:00 | 12 | 1000 | 137 2014-07-23 00:00:00 | 13 | 1000 | 80 2014-07-23 00:00:00 | 14 | 1000 | 163 2014-07-23 00:00:00 | 15 | 1000 | 86 2014-07-23 00:00:00 | 16 | 1000 | 145 2014-07-23 00:00:00 | 18 | 1832 | 51 2014-07-23 00:00:00 | 19 | 1000 | 75 2014-07-23 00:00:00 | 20 | 1000 | 103 2014-07-23 00:00:00 | 21 | 1000 | 92 2014-07-23 00:00:00 | 22 | 1000 | 62 2014-07-23 00:00:00 | 23 | 1000 | 53 2014-07-24 00:00:00 | 9 | 1000 | 82 2014-07-24 00:00:00 | 11 | 1000 | 51 2014-07-24 00:00:00 | 14 | 1000 | 72 2014-07-24 00:00:00 | 15 | 1000 | 76 2014-07-24 00:00:00 | 16 | 1000 | 107 2014-07-24 00:00:00 | 17 | 1000 | 82 2014-07-24 00:00:00 | 18 | 1000 | 92 2014-07-24 00:00:00 | 19 | 1000 | 71 2014-07-24 00:00:00 | 20 | 1000 | 58 2014-07-25 00:00:00 | 9 | 1000 | 51 2014-07-25 00:00:00 | 14 | 1000 | 98 2014-07-25 00:00:00 | 15 | 1000 | 55 2014-07-25 00:00:00 | 16 | 1000 | 141 2014-07-25 00:00:00 | 17 | 1000 | 167 2014-07-25 00:00:00 | 18 | 1000 | 68 2014-07-25 00:00:00 | 19 | 1000 | 64 2014-07-25 00:00:00 | 20 | 1000 | 87 2014-07-25 00:00:00 | 21 | 1000 | 61 2014-07-25 00:00:00 | 22 | 1000 | 59 2014-07-28 00:00:00 | 18 | 1000 | 74 2014-07-28 00:00:00 | 20 | 1000 | 77 2014-07-29 00:00:00 | 20 | 1000 | 68 2014-08-06 00:00:00 | 19 | 1000 | 54 2014-08-07 00:00:00 | 17 | 1000 | 59 2014-08-11 00:00:00 | 18 | 1000 | 65 2014-08-11 00:00:00 | 20 | 1000 | 58 2014-08-21 00:00:00 | 15 | 1000 | 63 2014-08-21 00:00:00 | 18 | 1000 | 86 2014-08-21 00:00:00 | 19 | 1000 | 104 2014-08-25 00:00:00 | 14 | 1000 | 63 2014-08-27 00:00:00 | 11 | 1000 | 98 2014-09-09 00:00:00 | 17 | 3031 | 52 2014-09-10 00:00:00 | 14 | 1000 | 54 2014-09-10 00:00:00 | 15 | 1000 | 57 2014-09-11 00:00:00 | 13 | 3125 | 112 2014-09-12 00:00:00 | 14 | 3125 | 59 2014-09-15 00:00:00 | 19 | 3125 | 98 2014-09-16 00:00:00 | 19 | 1000 | 69 2014-09-16 00:00:00 | 20 | 1000 | 74 2014-09-17 00:00:00 | 19 | 3125 | 84 2014-09-17 00:00:00 | 20 | 3125 | 173 2014-09-18 00:00:00 | 12 | 3125 | 124 2014-09-18 00:00:00 | 13 | 3125 | 106 2014-09-18 00:00:00 | 18 | 3300 | 66 2014-09-18 00:00:00 | 19 | 3125 | 155 2014-09-18 00:00:00 | 20 | 3125 | 144 2014-09-19 00:00:00 | 14 | 3300 | 51 2014-09-19 00:00:00 | 16 | 3125 | 53 2014-09-19 00:00:00 | 17 | 3125 | 148 2014-09-19 00:00:00 | 18 | 3125 | 122 2014-09-19 00:00:00 | 19 | 3125 | 96 2014-09-19 00:00:00 | 20 | 3125 | 114 2014-09-20 00:00:00 | 17 | 3125 | 64 2014-09-20 00:00:00 | 19 | 3125 | 89 2014-09-22 00:00:00 | 11 | 1000 | 75 2014-09-22 00:00:00 | 12 | 1000 | 75 2014-09-22 00:00:00 | 13 | 1000 | 109 2014-09-22 00:00:00 | 14 | 1000 | 62 2014-09-24 00:00:00 | 12 | 3125 | 58 2014-09-24 00:00:00 | 18 | 1000 | 63 2014-09-24 00:00:00 | 18 | 3031 | 55 2014-09-24 00:00:00 | 19 | 1000 | 72 2014-09-24 00:00:00 | 19 | 3031 | 70 2014-09-25 00:00:00 | 14 | 1000 | 75 2014-09-25 00:00:00 | 16 | 1000 | 69 2014-09-25 00:00:00 | 18 | 1000 | 94 2014-09-25 00:00:00 | 19 | 1000 | 109 2014-09-25 00:00:00 | 19 | 3459 | 59 2014-09-25 00:00:00 | 20 | 3459 | 69 2014-09-26 00:00:00 | 17 | 1000 | 73 2014-10-02 00:00:00 | 7 | 3597 | 166 2014-10-02 00:00:00 | 8 | 3597 | 535 2014-10-02 00:00:00 | 9 | 3597 | 546 2014-10-02 00:00:00 | 10 | 3597 | 542 2014-10-02 00:00:00 | 11 | 3597 | 540 2014-10-02 00:00:00 | 12 | 3597 | 991 2014-10-02 00:00:00 | 13 | 3597 | 130 2014-10-03 00:00:00 | 13 | 1000 | 62 2014-10-07 00:00:00 | 14 | 3591 | 56 2014-10-08 00:00:00 | 16 | 3833 | 53 2014-10-08 00:00:00 | 21 | 1000 | 135 2014-10-08 00:00:00 | 22 | 1000 | 105 2014-10-09 00:00:00 | 19 | 1000 | 75 2014-10-09 00:00:00 | 20 | 3833 | 63 2014-10-09 00:00:00 | 23 | 1000 | 104 2014-10-10 00:00:00 | 0 | 1000 | 59 2014-10-10 00:00:00 | 14 | 1000 | 116 2014-10-12 00:00:00 | 22 | 1000 | 70 2014-10-14 00:00:00 | 18 | 3986 | 60 2014-10-14 00:00:00 | 22 | 3986 | 53 2014-10-15 00:00:00 | 0 | 1000 | 70 2014-10-15 00:00:00 | 18 | 1887 | 61 2014-10-15 00:00:00 | 22 | 1887 | 73 2014-10-16 00:00:00 | 12 | 1000 | 101 2014-10-17 00:00:00 | 16 | 1000 | 65 2014-10-17 00:00:00 | 20 | 1000 | 77 2014-10-17 00:00:00 | 21 | 1000 | 71 2014-10-18 00:00:00 | 7 | 1000 | 53 2014-10-19 00:00:00 | 17 | 1000 | 130 2014-10-19 00:00:00 | 18 | 1000 | 72 2014-10-19 00:00:00 | 19 | 1000 | 150 2014-10-19 00:00:00 | 20 | 1000 | 142 2014-10-19 00:00:00 | 21 | 1000 | 139 2014-10-20 00:00:00 | 2 | 1000 | 63 2014-10-20 00:00:00 | 12 | 1000 | 81 2014-10-20 00:00:00 | 14 | 1000 | 60 2014-10-21 00:00:00 | 18 | 4069 | 52 2014-10-23 00:00:00 | 21 | 1000 | 91 (155 rows) Elapsed: 0.787s Backup taken: 2014-10-24 02:57:56.315568