SQL 获取过去 24 小时内每小时插入的记录数

Question

我正在尝试编写一个SQL查询，我需要在其中获取过去24小时内每小时插入的记录数。我在某处偶然发现了这个查询：

SELECT HOUR(timeStamp), COUNT(*)
FROM `events`
WHERE timeStamp > DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY HOUR(timeStamp)
ORDER BY HOUR(timeStamp)

原始表：

+---------+----------+-----------+--------------+---------------------+------------------------+
| eventID | personID | eventName | eventContent | timeStamp           | dateModified           |
+---------+----------+-----------+--------------+---------------------+------------------------+
|       1 |        4 | Login     | Login        | 26-12-2019 17:43:24 | 2019-12-26 12:13:23 pm |
|       2 |        1 | Login     | Login        | 26-12-2019 17:43:39 | 2019-12-26 12:13:38 pm |
|       3 |        1 | Button    | Click        | 26-12-2019 17:43:40 | 2019-12-26 12:13:38 pm |
|       4 |        1 | Login     | Login        | 26-12-2019 17:43:56 | 2019-12-26 12:13:55 pm |
|       5 |        1 | Mouse     | Moved        | 26-12-2019 17:43:57 | 2019-12-26 12:13:56 pm |
|       6 |        4 | Login     | Login        | 26-12-2019 17:44:18 | 2019-12-26 12:14:18 pm |
|       7 |        4 | Logged Out| Logged Out   | 26-12-2019 17:44:20 | 2019-12-26 12:14:19 pm |
|       8 |        5 | Login     | Login        | 26-12-2019 17:49:42 | 2019-12-26 12:19:41 pm |
|       9 |        5 | Message   | Hello        | 26-12-2019 17:49:44 | 2019-12-26 12:19:43 pm |
|      10 |        5 | Login     | Login        | 26-12-2019 17:49:57 | 2019-12-26 12:19:57 pm |
|      11 |        1 | Login     | Login        | 26-12-2019 17:58:20 | 2019-12-26 12:28:20 pm |
|      12 |        8 | Login     | Login        | 26-12-2019 18:6:32  | 2019-12-26 12:36:31 pm |
|      13 |       54 | Login     | Login        | 26-12-2019 18:6:50  | 2019-12-26 12:36:49 pm |
|      14 |       29 | Login     | Login        | 26-12-2019 18:7:11  | 2019-12-26 12:37:11 pm |
|      15 |        1 | Message   | Hello        | 27-12-2019 13:51:48 | 2019-12-27 08:21:49 am |
|      16 |        1 | Login     | Login        | 27-12-2019 13:51:50 | 2019-12-27 08:21:52 am |
|      17 |        1 | Login     | Login        | 27-12-2019 13:52:26 | 2019-12-27 08:22:28 am |
|      18 |        1 | Login     | Login        | 27-12-2019 13:54:59 | 2019-12-27 08:25:00 am |
+---------+----------+-----------+--------------+---------------------+------------------------+

但是 SQL 给我的输出为零：

object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(2) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }

我期望的输出是 24 个数字，它们的位置每小时指示一次。

例如：12 2 4 5 14 83 23 12 2 5 7 9 56 0 1 23 54 27 45 14 0 65 23 15(这些不是表中的实际数字。这是格式(

Answer 1

您的时间戳是一个字符串 要成功使用 hour 函数，您需要使用 str_to_date 进行转换，例如 hour(str_to_date('26-12-2019 17：43：24'，'%d-%m-%Y %k：%i：%s'((