CODEWITHSUNDEEP
mysqlselect
Andrew
Andrew

Reputation: 233

SQL SELECT WHERE with date and time

I have a SELECT query where I want to find all rows whose DATE and TIME are between 2011-12-11 23:00:00 and 2011-12-12 23:00:00 I try to do it with WHERE but row is empty

WHERE (date >= '2011-12-11' AND time > '23:00:00' )
AND   (date <  '2011-12-12' AND time < '23:00:00' )

Pls, any good suggestion how to change this?

Upvotes: 4

Views: 71818

Answers (5)

Andrew
Andrew

Reputation: 233

ha, and I have the solution without rebuild the dbase - it's working :))

WHERE 
CONCAT(date,' ',time) >= '2011-12-11 23:00:00' 
AND   
CONCAT(date,' ',time) <  '2011-12-12 23:00:00'

Maybe it helps for someone.

thanks for all helping people, brgs

Upvotes: 2

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Let's make sure of certain things

  • You need to get rid of the idea of separate date and time fields when searching
  • You need to create an additional column in your table called date_time (type DATETIME) which combines the two fields.
  • You should probably ditch the separate date and time fields and have just date_time
  • You can then create an index on date_time

Here is the command to do that

ALTER TABLE yourtable ADD INDEX date_time (date_time);

Once you do these things, THEN you can create a query with a WHERE clause that looks like this:

WHERE date_time >= '2011-12-11 23:00:00'
AND   date_time <  '2011-12-12 23:00:00'

If you cannot combine the date and time fields, you can still create an index

ALTER TABLE yourtable ADD INDEX date_time (date,time);

Given that situation, you can create a query with a WHERE clause that looks like this:

WHERE (date >= '2011-12-11' AND time >= '23:00:00')
AND   (date <= '2011-12-12' AND time <  '23:00:00')

The EXPLAIN plan for either situation should result in a fast execution of the query with the use of the date_time index.

Give it a Try !!!

Upvotes: 0

Brian Hoover
Brian Hoover

Reputation: 7991

Yup, that's pretty much not going to work. Show me all rows where time is greater than 11 pm and time is less that 11 pm. Time and Date are different fields?

You'll have to be a little more clever building up the query:

WHERE  (date = '2011-12-11' AND  time > '23:00:00' )
  or  ( date = '2011-12-12' AND time < '23:00:00' )

for a 24 hour window, you just need to have 2 clauses. If you want more than a 24 hour window, you'll need three clauses, one for the start date, one for the end date and one for all the dates in between:

WHERE  (date = '2011-12-11' AND  time > '23:00:00' )
  or  ( date = '2011-12-13' AND time < '23:00:00' )
  or (date >='2011-12-12' and date  < '2011-12-13')

Upvotes: 2

JM4
JM4

Reputation: 6788

You could use:

SELECT * FROM table WHERE DATETIME(date) BETWEEN '2011-11-11 23:00:00' AND '2011-12-13 23:00:00'

or separate:

SELECT * FROM table WHERE DATETIME(date) > '2011-12-11 23:00:00' AND DATETIME(date) < '2011-12-13 23:00:00'


EDIT:

I am not sure I understand what you are trying to achieve here or how your DB is laid out but assuming date and time are separate fields:

SELECT * FROM table WHERE DATETIME(concat(DATE(date),' ',TIME(time))) BETWEEN '2011-11-11 23:00:00' AND '2011-12-13 23:00:00'

I haven't tested but this may work.

Upvotes: 11

Randy
Randy

Reputation: 16677

hard to tell without the complete query. also assuming that the date column is actually a date type(?) you would usually do something like TO_DATE('2012-12-11','yyyy-mm-dd') to convert to date types in the comparison.

Upvotes: 0

Related Questions