按时间选择每天12:00:00到18:00:00之间的记录

发布于 2024-11-28 11:28:27 字数 256 浏览 2 评论 0原文

选择表中时间戳为 2011-08-01-

12:00:00

我尝试使用以下代码

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

的所有记录：但无法让它工作

原文

I've tried to select all records in a table with the timestamp in the dateformat 2011-08-01-

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一萌ing 2024-12-05 11:28:27

这里有两个问题：

您不能在 where 子句中引用列别名。相反，您必须在 where 子句中重复计算
使用 TIME() 函数提取数据时间的时间部分

解决了这两个问题后，您将得到：

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'

作为一个选项，如果您不这样做实际上不需要 select 中的时间值，您可以将其删除并将其放在 where 子句中。另外，如果更适合，您可以使用 HOUR() 函数。通过这两项更改，您的查询将简化为：

select *
FROM vbsk_dk_02.fab_master_flyvedata
where HOUR(f.start_tid) between 12 and 18

这更加简洁:)

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

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:

select *
FROM vbsk_dk_02.fab_master_flyvedata
where HOUR(f.start_tid) between 12 and 18

which is a lot neater :)

回复收藏 0 原文

巾帼英雄 2024-12-05 11:28:27

如果您已将时间存储在“时间戳”或“日期时间”类型的列中，则可以选择小时之间的一系列记录，如下所示：

select * from testTable where hour(`timeStampCol`) >= 12 and hour(`timeStampCol`) <= 18

我使用此设置对此进行了测试：

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')

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')

回复收藏 0 原文

~没有更多了~