两个日期两次之间的MySQL查询 [英] mySQL query between two dates and two times

问题描述

我想查询一个mySQL表以提取两次和两次之间的数据.我知道如何使用"between"调用对单个"datetime"列执行此操作，但是我的列是一个"date"列和一个"time"列.我在网上可以找到的所有解决方案都是针对单个datetime列的.

I would like to query a mySQL table to pull out data between two dates and two times. I know how to do this for a single "datetime" column using the "between" call but my columns are one "date" column, and one "time" column. All the solution I can find online are for single datetime columns.

我的范围从15:30的第1天"到15:14的第1 + 1天

My ranges go from "day1" at 15:30 to day1+1day at 15:14

到目前为止，我可以得到以下范围(有效):

So far I can get the following range (which works):

SELECT time,
       close 
  FROM intraday_values 
 WHERE date="2005-03-01" 
   and time between "15:30" and "23:59"

但是我显然需要合并两个日期和两次.我尝试了以下操作，但收到错误消息:

But I obviously need to incorporate 2 dates and two times. I have tried the following but get an error:

SELECT time,
       close 
  FROM intraday_values 
       between date="2005-03-01" 
   and time="15:30" 
   and date="2005-03-02" 
   and time = "15:14"

有人可以帮助我正确地制定查询吗?非常感谢

Could someone help me formulate the query correctly? Many thanks

推荐答案

不确定您的日期字段是否已编入索引.如果它们是其他人给出的"concat"示例，则可能效果不佳.

Not sure if your date field is indexed. If they are then the "concat" examples others have given may not perform very well.

或者，您也可以使用以下形式的查询:

As an alternative you can use a query of the form:

select * 
  from foo 
 where (date > lower_date and date < upper_date) -- technically this clause isn't needed if they are a day apart
    or (date = lower_date and time >= lower_time)
    or (date = upper_date and time <= upper_time)

这不是很漂亮，但是它可以工作，并且允许mysql利用date字段上的索引(如果存在的话).

It's not pretty but it works and will allow mysql to make use of indexes on the date field if they exist.

因此您的查询将是

SELECT time,
       close 
  FROM intraday_values 
 where (date > "2005-03-01" and date < "2005-03-02")
    or (date = "2005-03-01" and time >= "15:30")
    or (date = "2005-03-02" and time <= "15:14")

这篇关于两个日期两次之间的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！