CODEWITHSUNDEEP
mysql
John Magnolia
John Magnolia

Reputation: 16793

mysql select closest result from todays date past/future

I am sure this is a common task although all of the examples I have looked at dont help me solve this issue.

I have 2 tables:

  1. events
  2. schedules with foreign key to events. With schedule_datetime_from and schedule_datetime_until

Q. When selecting all of the events, how would I also fetch/join the first closest schedule based on todays date? E.g only return the most relevant schedule.

NOTE: There maybe more than one schedule for each event. The schedule may also be in the past.

E.g e.schedule_datetime_from >= NOW() OR schedule_datetime_until > NOW() would return only the future schedules, but how do I also return schedules in the past. Or do I need to use a ORDER BY + LIMIT 1 to achieve this?

Upvotes: 1

Views: 692

Answers (3)

Ash501
Ash501

Reputation: 331

This has worked for me:

SELECT * FROM table WHERE DATE(date_column) BETWEEN DATE(CURDATE()) AND DATE(CURDATE()+7);

This will give you a weeks heads up!

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select e.*, s.*
from 
    events e
    inner join 
    schedules s on e.id = s.event_id
order by 
    abs(unix_timestamp(schedule_datetime_from) - unix_timestamp(now())))
limit 1

Upvotes: 2

Falantar014
Falantar014

Reputation: 405

Well if you want to select a group of events between dates you can use

SELECT * FROM events WHERE date BETWEEN 'mm-dd-yyyy' AND 'mm-dd-yyyy'

If you want to select the most recent date you can add

ORDER BY date DESC LIMIT 1

Upvotes: 0

Related Questions