CODEWITHSUNDEEP
mysqlsql
s666
s666

Reputation: 537

mySQL query between two dates and two times

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

Upvotes: 3

Views: 15210

Answers (2)

Philip Couling
Philip Couling

Reputation: 14932

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

Upvotes: 6

sumit
sumit

Reputation: 15464

Use concat to combine these two column and cast it to datetime for the best results

SELECT 
time,close 
FROM 
intraday_values 
where  
cast(concat(date," ",time)  as datetime) 
between 
cast("2005-03-01 15:30") as datetime 
and cast("2005-03-02 15:14") as datetime

Upvotes: 0

Related Questions