CODEWITHSUNDEEP
sqlruby-on-railsrubydate
user984621
user984621

Reputation: 48453

Rails - how to fetch from database the closest records according to date?

I have in database stored records like this:

ID| date_column
1 | 2013-02-10 10:00:00
2 | 2013-02-10 11:00:00
3 | 2013-02-10 12:00:00
4 | 2013-02-10 13:00:00
5 | 2013-02-11 14:00:00
6 | 2013-02-11 22:00:00

I am trying to fetch 3 records, which are the closest ones to the today's date. For example, let's suppose today is 2013-02-10, so I would need to fetch 3 records with today's date (2013-02-10 11:00:00, 2013-02-10 12:00:00, 2013-02-10 13:00:00), but for example if today is 2013-02-15, I would need to get the closest 3 records, which are in this case 2013-02-10 13:00:00, 2013-02-11 14:00:00 and 2013-02-11 22:00:00?

Could you help me, please, how to do that?

Thank you

Upvotes: 1

Views: 1642

Answers (2)

Patrick
Patrick

Reputation: 136

This will get the 3 closes to a specific time, which I'm using noon on your target day. This will ensure today's results come ahead of any other day.

TableObjectName.all(order: "abs(timediff('2013-02-10 12:00:00', date_column))", limit: 3).

Upvotes: 0

Jack Marchetti
Jack Marchetti

Reputation: 15754

Assuming you're using ActiveRecord right?

This query will grab the three closest dates to "now" in the past:

time_to_check = Time.now
TableObjectName.where("date_column <= ?", time_to_check ).limit(3).order("date_column desc")

This query will grab the three closest dates to "now" in the future:

TableObjectName.where("date_column >= ?", time_to_check ).limit(3).order("date_column desc")

The only thing I can think of and this seems inefficient, but I can't think of a SQL way of doing this, is you now have two arrays basically with six records.

Three in the future (if there are any) Three in the past (if there are any).

I'd then loop through each and compare how much time has elapsed between each record from the timeToCheck variable.

Whichever three have the smallest amount of time difference, Add those to your final array.

Upvotes: 1

Related Questions