MySQL 每天12:00:00到18:00:00之间按时间选择记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6973844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select records by time in interval between 12:00:00 and 18:00:00 on every day
提问by user857558
I've tried to select all records in a table with the timestamp in the dateformat 2011-08-01-
我试图选择表中的所有记录，时间戳为日期格式 2011-08-01-
12:00:00
12:00:00
Using the following code:
使用以下代码：
SELECT f.`fly_reg`, RIGHT(f.`start_tid`,8) AS st, f.`start_hight`
FROM vbsk_dk_02.fab_master_flyvedata f
Where st between 12:00:00 AND 18:00:00
But can't get it to work
但无法让它工作
回答by Bohemian
You've got two issues here:
你这里有两个问题：
- You can't refer to column aliases in the where clause. Instead, you have to repeat your calculation in the where clause
- Use the
TIME()function to extract the time part of the datatime
- 您不能在 where 子句中引用列别名。相反，您必须在 where 子句中重复计算
- 使用
TIME()函数提取datatime的时间部分
With those two issues addressed, you get:
解决这两个问题后，您将获得：
select
f.fly_reg,
TIME(f.start_tid) AS st,
f.start_hight
FROM vbsk_dk_02.fab_master_flyvedata f
where TIME(f.start_tid) between '12:00:00' AND '18:00:00'
As an option, if you don't actually need the time value in the select, you can remove it and just have it in the where clause. Also, you can use the HOUR()function if that suits better. With those two changes in, your query would simplify to:
作为一种选择，如果您实际上不需要选择中的时间值，您可以将其删除，并将其放在 where 子句中。此外，HOUR()如果更适合，您可以使用该功能。通过这两个更改，您的查询将简化为：
select *
FROM vbsk_dk_02.fab_master_flyvedata
where HOUR(f.start_tid) between 12 and 18
which is a lot neater :)
这更整洁:)
回答by TJDJD
If you have stored the time in a column of type "Timestamp" or "Datetime", you can select a range of records between hours like this:
如果您已将时间存储在“时间戳”或“日期时间”类型的列中，您可以选择小时之间的一系列记录，如下所示：
select * from testTable where hour(`timeStampCol`) >= 12 and hour(`timeStampCol`) <= 18
I tested this with this setp up:
我用这个设置测试了这个：
CREATE TABLE `cm`.`testTable` (
`timeStampCol` TIMESTAMP NOT NULL,
`dateTimeCol` DATETIME NOT NULL
)
ENGINE = MyISAM
COMMENT = 'Delete this table';
insert into testTable values ('2010-01-01 14:52:00', '2010-01-01 14:52:00')
insert into testTable values ('2010-01-01 19:48:00', '2010-01-01 19:48:00')
