选择两次mysql之间的时间 [英] Select time between two times mysql

问题描述

我必须检查特定时间是否介于两次(打开和关闭时间)之间.

I have to check if a specific time is between two times (opening and closing time).

打开和关闭时间保存为数据库中与工作日ID(而不是日期)相关的开始"和结束".

The opening and closing times are saved in a database as "start" and "end" related to a weekday id and not to a date.

所以我的问题是，我有两种情况，第一种情况是结束时间大于开始时间，例如end = 19:00:00和start = 09:00:00

So my problem is, that I have two different cases first case is that the end-time is bigger than start time like if end=19:00:00 and start=09:00:00

但它也可以是end = 06:00:00和start = 20:00:00

but it also can be end=06:00:00 and start=20:00:00

那么检查时间是否介于两次之间的性能方法是什么?

So what is a performance way to check if a time is between the two times?

我的sql看起来像这样:

My sql looks like this:

SELECT * 
      FROM opening
      WHERE weekday_id = :weekday
      AND start <=  :time
      AND end >=  :time 

推荐答案

在性能方面，没有一种很好的方式来处理此问题.正确的where语句是:

There is not a great way performance-wise to handle this. The correct where statement is:

where (start <= end and :time between start and end) or
      (end < start and :time not between end and start)

当然，您不必使用between，可以将其扩展:

Of course, you don't have to use between, you can expand this out:

where (start <= end and :time >= start and :time <= end) or
      (end < start and (:time <= end or :time >= start))

这篇关于选择两次mysql之间的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！