我有一个查询,显示了1小时的时间间隔。我的问题是,当它设置为1小时,它显示完美的数据,从上午12点到晚上11点
SELECT
FROM_UNIXTIME(UNIX_TIMESTAMP(t_stamp) - MOD(UNIX_TIMESTAMP(t_stamp), 3600)) as Tstamp,
Col1,
Col2
FROM table
WHERE DATE(t_stamp) BETWEEN '2018-10-15' AND '2018-10-15'
GROUP BY Tstamp
ORDER BY t_stamp ASC
它的输出类似于
Tstamp Col1 Col2
2018-10-15 00:00:00 73.43 72.12
2018-10-15 01:00:00 73.29 71.96
2018-10-15 02:00:00 73.43 72.21
.
.
.
.
.
2018-10-15 16:00:00 74.24 72.85
2018-10-15 17:00:00 74.37 72.85
2018-10-15 18:00:00 74.20 72.64
2018-10-15 19:00:00 74.01 72.42
2018-10-15 20:00:00 73.80 72.42
2018-10-15 21:00:00 73.73 72.34
2018-10-15 22:00:00 73.59 72.13
2018-10-15 23:00:00 73.51 72.12
但是,当我将间隔设置为3小时或8小时时,它不会在午夜开始,例如,如果选择的日期是2018-10-15,间隔设置为3小时,它会在2018-10-14 11:00 pm开始,而不是2018-10-15 12:00 am
SELECT
FROM_UNIXTIME(UNIX_TIMESTAMP(t_stamp) - MOD(UNIX_TIMESTAMP(t_stamp), 10800)) as Tstamp,
Col1,
Col2
FROM table
WHERE DATE(t_stamp) BETWEEN '2018-10-15' AND '2018-10-15'
GROUP BY Tstamp
ORDER BY t_stamp ASC
这是间隔为3小时时的输出
Tstamp Col1 Col2
2018-10-14 23:00:00 73.43 72.12
2018-10-15 02:00:00 73.43 72.21
2018-10-15 05:00:00 73.73 72.42
2018-10-15 08:00:00 73.29 71.96
2018-10-15 11:00:00 73.36 72.34
2018-10-15 14:00:00 73.98 72.64
2018-10-15 17:00:00 74.37 72.85
2018-10-15 20:00:00 73.80 72.42
2018-10-15 23:00:00 73.51 72.12
我希望这样的输出,当间隔是3小时8小时,如果选择的日期是2018-10-15,它将在午夜12点开始,而不是前一天。
Tstamp Col1 Col2
2018-10-15 00:00:00 73.43 72.21
2018-10-15 03:00:00 73.72 72.99
2018-10-15 06:00:00 73.34 72.12
2018-10-15 09:00:00 73.43 72.77
2018-10-15 12:00:00 73.29 72.42
2018-10-15 15:00:00 74.15 72.76
2018-10-15 18:00:00 74.20 72.64
2018-10-15 21:00:00 73.73 72.34
.
.
.
非常感谢你的帮助。谢谢您
结构和样本数据
CREATE TABLE `table` (
`Col1` float DEFAULT NULL,
`Col2` float DEFAULT NULL,
`t_stamp` datetime DEFAULT NULL,
KEY `tablet_stampndx` (`t_stamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `table`
(`Col1`,
`Col2`,
`t_stamp`)
VALUES
('73.43','72.12','2018-10-15 00:00:00'),
('73.29','71.96','2018-10-15 01:00:00'),
('73.43','72.21','2018-10-15 02:00:00'),
('73.72','72.99','2018-10-15 03:00:00'),
('73.80','72.55','2018-10-15 04:00:00'),
('73.73','72.42','2018-10-15 05:00:00'),
('73.34','72.12','2018-10-15 06:00:00'),
('73.94','73.20','2018-10-15 07:00:00'),
('73.29','71.96','2018-10-15 08:00:00'),
('73.43','72.77','2018-10-15 09:00:00'),
('73.43','72.12','2018-10-15 10:00:00'),
('73.36','72.34','2018-10-15 11:00:00'),
('73.29','72.42','2018-10-15 12:00:00'),
('73.51','72.34','2018-10-15 13:00:00'),
('73.98','72.64','2018-10-15 14:00:00'),
('74.15','72.76','2018-10-15 15:00:00'),
('74.24','72.85','2018-10-15 16:00:00'),
('74.37','72.85','2018-10-15 17:00:00'),
('74.20','72.64','2018-10-15 18:00:00'),
('74.01','72.42','2018-10-15 19:00:00'),
('73.80','72.42','2018-10-15 20:00:00'),
('73.73','72.34','2018-10-15 21:00:00'),
('73.59','72.13','2018-10-15 22:00:00'),
('73.51','72.12','2018-10-15 23:00:00')
到目前为止,我已经尝试使用date\子函数,但它并没有真正给我想要的输出。我有 10-15
在日期间隔上选择,但显示的数据来自 10-14
这不是我想看到的
SELECT
t_stamp,
Col1,
Col2
FROM table
WHERE t_stamp BETWEEN DATE_SUB('2018-10-15 00:00:00', INTERVAL 3 HOUR)
AND DATE_ADD('2018-10-15 23:59:59', INTERVAL 3 HOUR)
GROUP BY t_stamp
ORDER BY t_stamp ASC
2018-10-14 21:02:17 73.50 72.21
2018-10-14 21:07:17 73.51 72.21
2018-10-14 21:12:17 73.50 72.21
2018-10-14 21:17:17 73.50 72.19
2018-10-14 21:22:17 73.50 72.21
2018-10-14 21:27:17 73.50 72.21
1条答案
按热度按时间js81xvg61#
这就是你要找的吗?