mysql查询在两者之间的,两个日期两次之间的MySQL查询

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.

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

解决方案

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)

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

So your query would be

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