[SOLVED] help with date query

[dadamssg]

i am trying to pull up events that are going on today and have not ended yet...ive tried countless queries but CANNOT seem to get anywhere. below is some sample data ive put in my db and i queried it around 9:55 and it pulled up nothing...

 

      ID      created datetime                start datetime            end datetime

      78 |2009-03-01 09:31:47| ---|2009-03-01 09:45:00| 2009-03-01 09:55:00|   

      79 |2009-03-01 09:32:19| ---|2009-03-01 10:00:00| 2009-03-01 10:20:00|

      80 |2009-03-01 09:32:50|--- |2009-03-01 01:00:00| 2009-03-01 09:35:00| 

      77 |2009-03-01 09:31:10| ---|2009-03-01 09:30:00| 2009-03-01 09:40:00|

 

the query i tried

 

SELECT * FROM test WHERE DATE(start) <= CURDATE() AND DATE(end) >= CURDATE() and DATE(end) >= NOW() ORDER BY start ASC

 

i want to pull all events today....but then wean off the ones that have already ended, so just pull up all events happening now or going to happen

 

[Mchl]

What do you get with this?

SELECT * FROM test WHERE DATE(start) <= CURDATE() AND DATE(end) >= CURDATE() AND end > NOW()

[dadamssg]

i think that worked...im gonna put some more items in my db and check more, could you or someone tell me if theres any difference in DATE(end) and just end as in

 

SELECT * FROM test WHERE DATE(start) <= CURDATE() AND DATE(end) >= CURDATE() AND end > NOW()

[dadamssg]

also if i wanted to pull up items that only last 24 hrs, how would you suggest goin about tackling that? should i make another column that calculates the difference between start and end timestamps?

[fenway]

What wrong with " start > NOW() - INTERVAL 24 HOUR", or something similar?

[Mchl]

i think that worked...im gonna put some more items in my db and check more, could you or someone tell me if theres any difference in DATE(end) and just end as in

 

SELECT * FROM test WHERE DATE(start) <= CURDATE() AND DATE(end) >= CURDATE() AND end > NOW()

 

'end' in your table is DATETIME. DATE() gets only date part of it. So if you do

DATE(end) >= NOW()

It's like doing

DATE(2009-03-01 09:55:00) >= 2009-03-01 00:00:00

that is

2009-03-02 >= 2009-03-01

 

so this would take all events ending today (without taking care of an hour)

 

[edit]

wrong, wrong!

 

DATE(end) >= NOW()

is like

DATE(2009-03-01 09:55:00) >= 2009-03-01 17:59:00  <- current time

that is

2009-03-01 00:00:00 >= 2009-03-01 17:59:00  which will be always false (except on midinight)

 

[dadamssg]

thanks for clearing that up Mchl! Fenway, i didn't know you could use anything like that in MySql, it doesn't seem like that would check the 'end' column to check the length of the item though...?

[Mchl]

I edited my post, as it was wrong

[fenway]

thanks for clearing that up Mchl! Fenway, i didn't know you could use anything like that in MySql, it doesn't seem like that would check the 'end' column to check the length of the item though...?

Sorry, I don't follow....

[Mchl]

I guess dadamssg wants to select events with duration time (i.e. end - start) equal 24 hours.

See TIMEDIFF() function.

[dadamssg]

how would one use TIMEDIFF()? i understand it calcs the differences but do you use it in a query like,

SELECT * FROM table WHERE TIMEDIFF(start, end) <= 24 hrs

or do you use it when creating a table column?

[Mchl]

For example

SELECT * FROM table WHERE TIMEDIFF(start, end) <= 240000

or

SELECT * FROM table WHERE TIMEDIFF(start, end) <= '24:00:00'

[dadamssg]

alright sweet...ok...now i have this which i want to pull up items occuring only today and not ended.

 

SELECT * FROM test WHERE DATE(start) <= CURDATE() AND DATE(end) >= CURDATE() AND end > NOW() ORDER BY start ASC

 

but this pulls up the following

 

ID            start                        end

84 |2009-03-01 01:00:00 | 2009-03-01 13:00:00 

85 |2009-03-01 13:00:00 | 2009-03-03 14:05:00

 

 

the first one is correct but the second shouldn't be pulled up because it ends March 3rd....not today

 

suggestions?

[Mchl]

DATE(end) = CURDATE()

[dadamssg]

perfect, and how about if i want to pull up items that are longer than 24 hrs? So...every event that is occuring today, whether it starts today, or ends today...but also don't want to pull up expired events?

 

how do i modify this?

 

SELECT * FROM test WHERE DATE(start) <= CURDATE() AND DATE(end) = CURDATE() AND end > NOW() ORDER BY start ASC

[dadamssg]

sorry to bump this, but help would be amazing!

[Mchl]

whether it starts today

DATE(start) = CURDATE()

 

ends today

DATE(end) = CURDATE()

 

don't want to pull up expired events

end > NOW()

 

[dadamssg]

thanks Mchl, is there any difference between that and this?

 

SELECT * FROM test WHERE start <= NOW() AND end >= NOW() ORDER BY start ASC

[Mchl]

there is

Let's say NOW() returns 2009-03-01 23:04:00

 

Your qeury will not show an event that starts at 2009-03-01 23:15:00

It will however show an event that started at 2009-02-27 15:00:00 and haven't ended yet. Not sure if this is what you want.

[dadamssg]

thats what i want but im not sure i get the logic...say, if the event started a couple days ago and doesn't end for a couple days, how come it would pull it up if the dates in start and end don't equal todays date? im trying to wrap my head around how start = CURDATE() but the actual date in the db is like 2009-02-27?

[Mchl]

Well... you posted

start <= NOW()

 

so

 

2009-02-27 15:00:00 <= 2009-03-01 23:15:00

[dadamssg]

alright thanks man! i really appreciate you keeping posting my answers!