从包含长时间段的表中选择每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-从timestamp列中选择间隔每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

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

Answer 1

这工作起来很好，只是使用完整的Group by，您需要使用一个aggrgage函数

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;

| Time_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<>在此拨弄