Hi
I have a table with two fields, EventID and EventDate.
Each EventID may have one or more dates attached to it (stored in the format YYYY-MM-DD). I want to run a query that returns any EventIDs where the latest date that an event runs is earlier than today.
"SELECT EventID FROM `eventDates` WHERE EventDate<'$today' ORDER BY EventID" returns all EventIDs earlier than the current date, and "SELECT EventID FROM `eventDates` WHERE EventDate IN (SELECT MAX(EventDate) FROM `eventDates` WHERE EventDate<'$today')" returns two results, though I'm not quite sure why I get those and not any others!
Does anybody have any pointers please?
MySQL - getting dates from a table earlier than today
Moderator: General Moderators
-
Eric Praline
- Forum Commoner
- Posts: 32
- Joined: Wed Aug 29, 2007 8:37 am
- mecha_godzilla
- Forum Contributor
- Posts: 375
- Joined: Wed Apr 14, 2010 4:45 pm
- Location: UK
Re: MySQL - getting dates from a table earlier than today
Hi,
Is the value in the EventID column meant to be unique? I don't quite understand what the issue is - based on what you're saying, the first query already does what you want. If the problem is that the EventID column is not unique, and your query is returning more than one result for the same EventID, then you can do this:
Also, to save a couple of lines of code you can use CURDATE() if you want MySQL to pre-populate your query with today's date - the only reason not to use functions like these in your queries is that they tie you to a specific database platform. If this isn't likely to be a problem, you can then use INTERVAL in conjunction with CURDATE() so that you don't have to manually calculate date ranges in your script, and can then have queries that do things like retrieve all the events occurring in the next 30 days, or retrieve all the events that occurred in the last 10 days, etc.
HTH,
Mecha Godzilla
Is the value in the EventID column meant to be unique? I don't quite understand what the issue is - based on what you're saying, the first query already does what you want. If the problem is that the EventID column is not unique, and your query is returning more than one result for the same EventID, then you can do this:
Code: Select all
SELECT DISTINCT(EventID) FROM `eventDates` WHERE EventDate < '$today';HTH,
Mecha Godzilla
-
Eric Praline
- Forum Commoner
- Posts: 32
- Joined: Wed Aug 29, 2007 8:37 am
Re: MySQL - getting dates from a table earlier than today
Sorry, I was in a bit of a rush when I posted my question and didn't have time to explain fully!
You are correct in saying that the EventIDs are not unique. I have another table that contains the event details, but this table only contains the dates that an event is taking place. So each event may occur only once, or across multiple dates.
ie
EventID EventDate
1 2013-06-02
2 2013-06-12
2 2013-06-13
3 2013-05-28
4 2013-06-15
4 2013-06-22
4 2013-06-29
5 2013-07-18
6 2013-07-23
6 2013-07-30
6 2013-08-06
7 2013-08-04
8 2013-06-03
8 2013-07-03
8 2013-08-03
So, I wish to get a list of EventIDs where the 'latest' date for each EventID is earlier than the current date eg EventIDs 1, 2, 3, 4 and 5 would be the results I want, whereas EventIDs 6, 7 and 8 contain dates that are later than the current date and are not required.
If I do the 'SELECT DISTINCT...' query it would also include EventIDs 6 and 8 because they have dates earlier than today.
I think the answer includes the use of 'MAX(EventID)' somehow, although I may be wrong
Thanks!
You are correct in saying that the EventIDs are not unique. I have another table that contains the event details, but this table only contains the dates that an event is taking place. So each event may occur only once, or across multiple dates.
ie
EventID EventDate
1 2013-06-02
2 2013-06-12
2 2013-06-13
3 2013-05-28
4 2013-06-15
4 2013-06-22
4 2013-06-29
5 2013-07-18
6 2013-07-23
6 2013-07-30
6 2013-08-06
7 2013-08-04
8 2013-06-03
8 2013-07-03
8 2013-08-03
So, I wish to get a list of EventIDs where the 'latest' date for each EventID is earlier than the current date eg EventIDs 1, 2, 3, 4 and 5 would be the results I want, whereas EventIDs 6, 7 and 8 contain dates that are later than the current date and are not required.
If I do the 'SELECT DISTINCT...' query it would also include EventIDs 6 and 8 because they have dates earlier than today.
I think the answer includes the use of 'MAX(EventID)' somehow, although I may be wrong
Thanks!
Re: MySQL - getting dates from a table earlier than today
Code: Select all
SELECT EventID, EventDate
FROM `events`
GROUP BY EventID
HAVING MAX(EventDate) < NOW()-
Eric Praline
- Forum Commoner
- Posts: 32
- Joined: Wed Aug 29, 2007 8:37 am
Re: MySQL - getting dates from a table earlier than today
Ah ha! I thought it was something fairly simple but couldn't work out the correct syntax... thank you!