MySQL选择:WHERE(现在时间)=表值和表值之间 [英] MySQL Select: WHERE (time now) = BETWEEN tablevalue AND tablevalue

问题描述

我正在寻找一种选择当前时间在行中两个设置值之间的行的方法.我已经建立了一个包含3列的表格，其中2列包含一个时间戳(HH:MM:SS)，另一列是一个字符串.有没有一种方法可以获取与当前时间相对应的字符串? 用更抽象的方式讲:

I'm looking for a way to select the row in which the current time is between two set values in the row. I've set up a table with 3 columns, 2 of them hold a timestamp (HH:MM:SS), the other one a string. Is there a way I can get the string corresponding to the current time? To put it in a more abstract way:

从TableName中选择字符串，在(下限时间值)和(下限时间值)之间(当前时间)；

SELECT String FROM TableName WHERE (Current Time) BETWEEN (Lower Limit Time Value) AND (Upper Limit Time Value);

所以基本上，基于当前时间，我的脚本应该输出正确的字符串.

So basically, based on the current time, my script should output the correct string.

我该如何处理? 谢谢！

How do I go about this? Thanks!

推荐答案

在MySQL中，时间戳是一个令人困惑的词.

In MySQL, timestamp is quite a confusing word.

如果它们是lowerlimit并且upperlimit是TIME从00:00:00到23:59:59的列:

If they are lowerlimit and upperlimit are TIME columns from 00:00:00 to 23:59:59:

SELECT  String
FROM    TableName
WHERE   CURTIME() BETWEEN lowerlimit AND upperlimit
        OR CURTIME() BETWEEN SUBTIME(upperlimit, '24:00:00') AND lowerlimit
        OR SUBTIME(CURTIME(), '24:00:00') BETWEEN SUBTIME(upperlimit, '24:00:00') AND lowerlimit

这将正确处理午夜过渡.

This will handle midnight transitions correctly.

这篇关于MySQL选择:WHERE(现在时间)=表值和表值之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！