mySQL：在表中使用BETWEEN？

发布于 2024-08-27 07:30:22 字数 854 浏览 4 评论 0原文

我有一个表格，其中包括一些学生小组名称、课程时间、日期名称（例如时间表）。我正在使用 C# 和 MySql，我想找到当用户按下表中的按钮时是哪一课。

我可以通过输入表中的精确值来找到它，例如 08:30 或 10:25。但我想获取系统时间并检查它是否在 08:30 到 10:25 或 10:25 到 12:30 之间。那我可以说这是第一课或者第二课。

我还有表包括 Table_Time 列有 5 条记录，如 08:20 、 10:25 、 12:20 等。我可以使用像 :

select Lesson_Time 
from mydb.clock 
where Lesson_Time between (current time)-30 AND (current time)+30

或者我可以在两列之间使用运算符（例如创建 Lesson_Time_Start 和 Lesson_Time_End）并比较当前时间，例如 Lesson_Start_Time 当前时间< Lesson_End_Time？

编辑： 更准确地说，问题是我如何检查当前时间是

我有表的

Time_ID  | Lesson_Time 
    1       08:30
    2       10:25
    3       12:20
    4       14:15
    5       16:10

哪条记录，当我按下某个按钮时，我想检查按下按钮时的当前时间是否等于哪个 Time_ID 例如，我在 09:15 按下按钮，它必须选择第 1 条记录，如果我在 12:00 按下按钮，它必须选择第二条记录。

原文

I have a table that includes some student group name, lesson time, day names like Schedule. I am using C# with MySql and I want to find which lesson is when user press button from table.

I can find it by entering the exact value like in the table, e.g. 08:30 or 10:25. But I want to make that getting system time and checking that it is between 08:30 and 10:25 or 10:25 and 12:30. Then I can say that it is the first or second lesson.

I have also table includes Table_Time column has 5 record like 08:20 , 10:25 , 12:20 so on.
Could I use like :

select Lesson_Time 
from mydb.clock 
where Lesson_Time between (current time)-30 AND (current time)+30

Or can I use between operator between two columns (like creating Lesson_Time_Start and Lesson_Time_End) and comparing current time like Lesson_Start_Time < current time < Lesson_End_Time?

EDIT:
More precisely ,problem is how can I check current time is which record

I have table like

Time_ID  | Lesson_Time 
    1       08:30
    2       10:25
    3       12:20
    4       14:15
    5       16:10

And When I press some button I want to check that current time when I pressed button is it equel to which Time_ID
Forexample I pressed button at 09:15 and it must select 1th record , if I pressed button at 12:00 it must select 2th record.

分享到QQ

分享到微博

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

发布评论

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

入画浅相思 2024-09-03 07:30:22

我会尝试这样的事情。

select
    Time_ID,
    Lesson_Time
from
    mydb.clock
where
    Lesson_Time <= TIME(NOW())
order by
    Lesson_Time DESC
limit 0, 1

该查询仅限于当前时间之前的课程时间记录，按时间降序排序，并且仅返回第一条记录，这应该是您想要的记录。（但请注意，午夜可能会使事情变得复杂。）

I'd try something like this.

select
    Time_ID,
    Lesson_Time
from
    mydb.clock
where
    Lesson_Time <= TIME(NOW())
order by
    Lesson_Time DESC
limit 0, 1

The query is limited to lesson time records prior to the current time sorted descending by time and returns only the first record, which should be the record you want. (Note, however, that midnight can complicate matters.)

回复收藏 0 原文

信愁 2024-09-03 07:30:22

我不完全确定你想做什么，但我认为你正在寻找 DATE_ADD 和 DATE_SUB：

SELECT Lesson_Time from mydb.clock
 WHERE Lesson_Time between DATE_SUB(NOW(), INTERVAL 30 MINUTE) AND 
                           DATE_ADD(NOW(), INTERVAL 30 MINUTE);

如果 Lesson_Time 是正确的 DATETIME 字段，这应该可以工作。

I'm not entirely sure what you want to do, but I think you are looking for DATE_ADD and DATE_SUB:

SELECT Lesson_Time from mydb.clock
 WHERE Lesson_Time between DATE_SUB(NOW(), INTERVAL 30 MINUTE) AND 
                           DATE_ADD(NOW(), INTERVAL 30 MINUTE);

provided Lesson_Time is a proper DATETIME field, this should work.

回复收藏 0 原文

~没有更多了~