Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- [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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
