Skip to content

Instantly share code, notes, and snippets.

@phanngoc

phanngoc/số lượng message theo giờ phút

Last active August 9, 2018 03:36
Show Gist options
  • Save phanngoc/5aebae509edc9f9370cbd9aed652504d to your computer and use it in GitHub Desktop.
Save phanngoc/5aebae509edc9f9370cbd9aed652504d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
số lượng message theo giờ phút
mysql> select count(id), TIME_FORMAT(created_at, "%H %i") from messages where created_at > '2018-08-08 20:10:00' and created_at <= '2018-08-08 20:35:00' group by TIME_FORMAT(created_at, "%H %i");
+-----------+----------------------------------+
| count(id) | TIME_FORMAT(created_at, "%H %i") |
+-----------+----------------------------------+
| 35 | 20 10 |
| 20 | 20 11 |
| 22 | 20 12 |
| 35 | 20 13 |
| 50 | 20 14 |
| 18 | 20 15 |
| 28 | 20 16 |
| 13 | 20 17 |
| 18 | 20 18 |
| 46 | 20 19 |
| 20 | 20 20 |
| 13 | 20 21 |
| 25 | 20 22 |
| 25 | 20 23 |
| 40 | 20 24 |
| 29 | 20 25 |
| 14 | 20 26 |
| 33 | 20 27 |
| 18 | 20 28 |
| 39 | 20 29 |
| 28 | 20 30 |
| 17 | 20 31 |
| 6 | 20 32 |
| 11 | 20 33 |
+-----------+----------------------------------+
24 rows in set (0.55 sec)
Raw
user mới theo giờ phút
mysql> select count(id), TIME_FORMAT(created_at, "%H %i") from threads where created_at > '2018-08-08 20:10:00' and created_at <= '2018-08-08 20:35:00' group by TIME_FORMAT(created_at, "%H %i");
+-----------+----------------------------------+
| count(id) | TIME_FORMAT(created_at, "%H %i") |
+-----------+----------------------------------+
| 12 | 20 10 |
| 18 | 20 11 |
| 13 | 20 12 |
| 13 | 20 13 |
| 13 | 20 14 |
| 9 | 20 15 |
| 8 | 20 16 |
| 12 | 20 17 |
| 11 | 20 18 |
| 12 | 20 19 |
| 8 | 20 20 |
| 2 | 20 21 |
| 9 | 20 22 |
| 5 | 20 23 |
| 8 | 20 24 |
| 6 | 20 25 |
| 7 | 20 26 |
| 10 | 20 27 |
| 13 | 20 28 |
| 8 | 20 29 |
| 8 | 20 30 |
| 4 | 20 31 |
| 3 | 20 32 |
| 8 | 20 33 |
| 4 | 20 34 |
+-----------+----------------------------------+
25 rows in set (0.09 sec)
Raw
user tương tác theo giờ phút
mysql> select count(id), TIME_FORMAT(updated_at, "%H %i") from threads where created_at > '2018-08-08 20:10:00' and created_at <= '2018-08-08 20:35:00' group by TIME_FORMAT(updated_at, "%H %i");
+-----------+----------------------------------+
| count(id) | TIME_FORMAT(updated_at, "%H %i") |
+-----------+----------------------------------+
| 2 | 20 10 |
| 1 | 20 11 |
| 2 | 20 12 |
| 1 | 20 13 |
| 3 | 20 14 |
| 1 | 20 15 |
| 2 | 20 16 |
| 6 | 20 17 |
| 4 | 20 18 |
| 3 | 20 19 |
| 4 | 20 20 |
| 4 | 20 21 |
| 6 | 20 22 |
| 8 | 20 23 |
| 3 | 20 24 |
| 12 | 20 25 |
| 12 | 20 26 |
| 8 | 20 27 |
| 8 | 20 28 |
| 7 | 20 29 |
| 13 | 20 30 |
| 19 | 20 31 |
| 20 | 20 32 |
| 24 | 20 33 |
| 35 | 20 34 |
| 11 | 20 35 |
| 1 | 20 37 |
| 1 | 20 41 |
| 1 | 20 44 |
| 1 | 21 42 |
| 1 | 22 36 |
+-----------+----------------------------------+
31 rows in set (0.09 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment