CODEWITHSUNDEEP
mysqlsqldatetimerange
Gooey
Gooey

Reputation: 4778

Mysql datetime between two columns

I've got two columns (both datetime) startDate and endDate in an events table. I am retrieving the current day using the the date() function of php.

This results in for example 2013-03-12.

Now there are three possibilities of events combined with dates that occur today:

  1. An event starts and also end on this day
  2. An event has started earlier and ends today
  3. An event starts today but ends in the future (>= 2013-03-13)

Now I'd like to break these all into separate queries as I'm not used to work with dates. I started with the first query, but I am already failing on that one. I've tried the following:

SELECT * FROM events WHERE (startDate= '2013-03-12' AND endDate= '2013-03-12')

aswell as:

SELECT * FROM events WHERE NOT (startDate < '2013-03-12' OR endDate > '2013-03-12')

I've tried to use DATE() aswell and to format dates like '2013-03-12%'.

I don't know why it doesn't work while i am sure there is at least 1 event that is taking place on the 12th. Any help is appreciated.

Upvotes: 5

Views: 23256

Answers (4)

Barmar
Barmar

Reputation: 780724

You can use the DATE() function as other answers suggested, but I think this makes it hard to use an index on the columns. Instead, you can include times in your comparisons:

SELECT *
FROM events
WHERE startDate BETWEEN '2013-03-12 00:00:00' AND '2013-03-12 23:59:59'
  AND endDate BETWEEN '2013-03-12 00:00:00' AND '2013-03-12 23:59:59'

Upvotes: 3

UWU_SANDUN
UWU_SANDUN

Reputation: 1193

NOTE THIS WHEN USING between on Mysql

date_column_name between 'startDate' AND 'endDate'

NOTE : you should want to insert +1 date to endDate . Because of when you insert 2015-05-18 date to endDate.you can not get data of 2015-05-18.So you need to plus one date to endDate.

Upvotes: 0

John Conde
John Conde

Reputation: 219804

Try using the MySQL's DATE() function to trim the date columns to the just the date parts:

SELECT * 
FROM events 
WHERE (DATE(startDate) = '2013-03-12' AND DATE(endDate)= '2013-03-12')

Upvotes: 7

Achrome
Achrome

Reputation: 7821

The DATETIME datatype in MySQL considers the time of the day as well, so, it will not match anything.

If you don't have any use of the time part, then you can simply reduce the datatype to DATE instead of DATETIME. If not, you can use the DATE() function to get rid of the time part and only consider the date part

Upvotes: 2

Related Questions