Mysql-在datetime列中搜索时间范围

Question

我需要从一个表中返回记录，其中列date_opened(日期时间(在特定的时间范围内，而不管日期是什么。我目前有这个

SELECT * FROM packages WHERE HOUR(date_opened) = 9

但我需要进一步深入。我需要date_opened在09:00到09:30之间时的结果。我怎样才能做到这一点？

编辑没想到会有人为此要求数据，但现在是：

CREATE TABLE `packages` (
`id` int(11) NOT NULL,
`user` int(11) NOT NULL,
`date_opened` datetime NOT NULL
);
INSERT INTO `packages` (`id`, `user`, `date_opened`) VALUES
(58, 3, '2019-08-26 09:43:25'),
(73, 3, '2019-08-30 09:43:48'),
(76, 3, '2019-09-03 09:46:33'),
(77, 3, '2019-09-03 09:57:34'),
(79, 3, '2019-09-04 09:46:58'),
(86, 3, '2019-09-06 09:34:13'),
(88, 3, '2019-09-09 09:42:08'),
(90, 3, '2019-09-11 09:42:04'),
(91, 3, '2019-09-12 09:40:05'),
(92, 3, '2019-09-12 09:59:31'),
(93, 3, '2019-09-13 09:56:12'),
(95, 3, '2019-09-16 09:41:44'),
(101, 3, '2019-09-19 09:52:01'),
(104, 3, '2019-09-20 09:54:20'),
(107, 3, '2019-09-23 09:37:47'),
(108, 3, '2019-09-24 09:25:07'),
(110, 3, '2019-09-25 09:25:46'),
(113, 3, '2019-09-26 09:42:42'),
(114, 3, '2019-09-27 09:50:44'),
(115, 3, '2019-09-30 09:25:16'),
(117, 3, '2019-10-03 09:42:21'),
(123, 3, '2019-10-10 09:25:08'),
(126, 3, '2019-10-14 09:50:07'),
(127, 3, '2019-10-15 09:55:12'),
(129, 3, '2019-10-16 09:48:47');
ALTER TABLE `packages`
ADD PRIMARY KEY (`id`),
ADD KEY `user` (`user`);

期望结果：

| id  | user | date_opened         |
| --- | ---- | ------------------- |
| 108 | 3    | 2019-09-24 09:25:07 |
| 110 | 3    | 2019-09-25 09:25:46 |
| 115 | 3    | 2019-09-30 09:25:16 |
| 123 | 3    | 2019-10-10 09:25:08 |

Answer 1

select * 
from packages 
where time(date_opened) between '09:00:00' AND '09:30:00';

https://www.db-fiddle.com/f/i1Sb7zkYf9izia17B5tLHE/0

再次存储时间与日期时间是有争议的，但8.0可能有一种更智能的索引时间和日期的方法；我不确定。