SQLite中基于DATETIME字段生成5分钟时间桶的问题

问题背景 #

已创建如下数据表并插入测试数据：

-- Create TABLE
DROP TABLE IF EXISTS TABLE3;
CREATE TABLE TABLE3 (
    DATETIME TEXT,
    TIME_BUCKET TEXT
);

-- Insert data into TABLE
INSERT INTO TABLE3 (DATETIME, TIME_BUCKET) VALUES
('2024-01-09T10:33:06.987',''),
('2024-01-09T10:33:06.987',''),
('2024-01-09T10:34:07.006',''),
('2024-01-09T10:38:07.161',''),
('2024-01-09T10:39:10.061',''),
('2024-01-09T10:40:21.279',''),
('2024-01-09T10:44:21.317',''),
('2024-01-09T10:49:21.448',''),
('2024-01-09T10:51:05.673','');

已成功用以下语句更新为1分钟时间桶：

UPDATE TABLE3
SET TIME_BUCKET = strftime('%Y-%m-%d %H:%M:00', DATETIME);

但尝试以下语句更新5分钟时间桶时，TIME_BUCKET被设为NULL（原因：SQLite的strftime不支持'start of minute/hour'修饰符）：

UPDATE TABLE3
SET TIME_BUCKET = strftime('%Y-%m-%d %H:%M:00', DATETIME, 'start of minute', '+5 minutes');

期望得到的5分钟时间桶格式示例：

DATETIME    TIME_BUCKET
2024-01-09T10:33:06.987 2024-01-09 10:30:00
2024-01-09T10:33:06.987 2024-01-09 10:30:00
2024-01-09T10:34:07.006 2024-01-09 10:30:00
2024-01-09T10:38:07.161 2024-01-09 10:35:00
2024-01-09T10:39:10.061 2024-01-09 10:35:00
2024-01-09T10:40:21.279 2024-01-09 10:40:00
2024-01-09T10:44:21.317 2024-01-09 10:40:00
2024-01-09T10:49:21.448 2024-01-09 10:45:00
2024-01-09T10:51:05.673 2024-01-09 10:50:00

正确的更新语句 #

通过Unix时间戳计算5分钟桶的起始时间，再转换为目标格式：

UPDATE TABLE3
SET TIME_BUCKET = strftime('%Y-%m-%d %H:%M:00', 
                           (strftime('%s', DATETIME) / 300) * 300, 
                           'unixepoch');

原理说明 #

1. strftime('%s', DATETIME)：将原始时间字符串转换为Unix时间戳（单位：秒）
2. / 300 * 300：300秒等于5分钟，通过整数除法取整，得到当前时间所在5分钟桶的起始时间戳
3. strftime('%Y-%m-%d %H:%M:00', ..., 'unixepoch')：将计算后的时间戳转换为目标格式的时间字符串

验证结果 #

执行上述更新语句后，查询表数据即可得到期望的5分钟时间桶结果。

内容的提问来源于stack exchange，提问作者Swap