mysql - 从包含较长时间段的表中选择每 N 小时间隔

Question

我有一个如下所示的列，从 2019 年到 2021 年显示 24 小时：

2019-01-01 00:00:00
2019-01-01 01:00:00
2019-01-01 02:00:00
2019-01-01 03:00:00
2019-01-01 04:00:00
2019-01-01 05:00:00
2019-01-01 06:00:00
2019-01-01 07:00:00
2019-01-01 08:00:00
...
2019-01-02 00:00:00
2019-01-02 01:00:00
2019-01-02 02:00:00
2019-01-02 03:00:00
2019-01-02 04:00:00
2019-01-02 05:00:00
2019-01-02 06:00:00
2019-01-02 07:00:00
2019-01-02 08:00:00
...

我怎么能每隔 4 小时选择一次时间点？我期望的是：

2019-01-01 00:00:00
2019-01-01 04:00:00
2019-01-01 08:00:00
...

我尝试了一些选择，例如：

select Time_UTC from t1
GROUP BY DATE(Time_UTC), hour(Time_UTC) DIV 4;

就像这个问题：MySQL - 从时间戳列中选择每 2 小时的间隔

但它返回一个错误：

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't1.Time_UTC' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我知道有一些方法可以解除限制。但我无权这样做。

标签： mysql

Answer 1

这工作得很好，只有完整的 Group by，你需要使用聚合函数

CREATE TABLE t1
    (`Time_UTC` varchar(19))
;
    
INSERT INTO t1
    (`Time_UTC`)
VALUES
    ('2019-01-01 00:00:00'),
    ('2019-01-01 01:00:00'),
    ('2019-01-01 02:00:00'),
    ('2019-01-01 03:00:00'),
    ('2019-01-01 04:00:00'),
    ('2019-01-01 05:00:00'),
    ('2019-01-01 06:00:00'),
    ('2019-01-01 07:00:00'),
    ('2019-01-01 08:00:00'),
    ('2019-01-02 00:00:00'),
    ('2019-01-02 01:00:00'),
    ('2019-01-02 02:00:00'),
    ('2019-01-02 03:00:00'),
    ('2019-01-02 04:00:00'),
    ('2019-01-02 05:00:00'),
    ('2019-01-02 06:00:00'),
    ('2019-01-02 07:00:00'),
    ('2019-01-02 08:00:00')

SELECT Time_UTC
FROM (
select MIN(Time_UTC) AS Time_UTC,DATE(Time_UTC) , hour(Time_UTC) DIV 4  
from t1
GROUP BY DATE(Time_UTC), (hour(Time_UTC) DIV 4)) t1
ORDER BY Time_UTC;

| 时间_UTC |
| :----------------- |
| 2019-01-01 00:00:00 |
| 2019-01-01 04:00:00 |
| 2019-01-01 08:00:00 |
| 2019-01-02 00:00:00 |
| 2019-01-02 04:00:00 |
| 2019-01-02 08:00:00 |

db<>在这里摆弄