How to use :conditions to compare (a datetime) with (datetime + 'number of days') ?

Question

I'm building an application that checks when a user needs to change the oil of one of his cars. Here's how it works:

1. The user enters the name of his car (e.g. 'Ford Mustang')
2. The user enters the time the car will run on one oil change (e.g. 3.months)
3. The user saves the car to the database
4. Repeat above process for all his other cars. (Yes, it's a wealthy user ;)

Now, when the user comes back to the site he wants to see a list of all his cars that are in need of an oil change. He then changes the oil and updates the 'oil_changed_at' property.

I'm using the following columns for the Car-model:

title:string
oil_changed_at:datetime
oil_expiration_timeframe:integer

My question: What is the preferred way to store this 'oil_expiration_timeframe' and what should my :conditions look like when I want to get a list of all cars that are in need of an oil change?

Keep in mind the oil_expiration_timeframe may vary from a few days to a few years. It will never be less than a day so I think I can just store the amount of days in that field. However, I'm not sure how to combine the integer and datetime in the query.

Answer 1

Adding a number to a DateTime adds that many days to the Date. For instance:

irb(main):005:0> DateTime.now.to_s
=> "2010-08-27T18:32:36-06:00"
irb(main):006:0> (DateTime.now + 7).to_s
=> "2010-09-03T18:32:48-06:00"

So I would suggest sticking with your original plan of keeping the offset stored as a number of days.

Answer 2

This is really a matter of knowing a bunch of SQL date functions. In MySQL, it would look like this:

:conditions => 
  "DATE_ADD(DATE(oil_changed_at),
            INTERVAL oil_expiration_timeframe DAY) <= CURDATE()"

The only downside with this is there's not a good way to optimize this query. If performance becomes an issue, it might be easiest to additionally store the current expiration date in the database.

Answer 3

If you index your oil_changed_at column, following approach is efficient.

Car.all(:conditions => [
  "oil_changed_at > DATE_SUB(?, INTERVAL oil_expiration_timeframe day)", Time.now
])

Better approach would be to add a new column(next_oil_change_date) to Car model to store the next oil change date and update the col upon changes to the Car model.

class Car < ActiveRecord::Base
  before_save :set_next_oil_change_date

  def set_next_oil_change_date
    self.next_oil_change_date = next_oil_change_date + 
                                  oil_expiration_timeframe.days
  end
end

Now you can write your query as:

Car.all(:conditions => [
  "next_oil_change_date > ? ", Time.now
])

Don't forget to index the next_oil_change_date column.

Answer 4

Based on wuputah's MySQL query code I came up with the following for SQLite:

:conditions => 'date(oil_changed_at, oil_expiration_timeframe||" days") <= date("now")'

Of course a database-agnostic approach is still preferred.