Rails query: Compare calculation of two attribute values

Question

I have a model, Product, which has both a :created_at timestamp and an :expiration_in_days attribute. Products are considered expired a certain number of days after their creation. How do I write a query that only returns products that have not expired?

Rails 4, Ruby 2.1, PG 0.17

I have been trying queries like this, with no success:

#product.rb
def self.not_expired
  where('created_at + expiration_in_days * 86400 >= ?', Time.now)
end

I have a pure Ruby version that works fine, it's just slower:

#product.rb
def self.not_expired
  select{ |p| (p.created_at.to_i + p.expiration_in_days * 86400) >= Time.now.to_i}
end

note, the 86400 calculation converts the the :expiration_in_days integer into seconds

Any pointers on more advanced Rails queries than the documentation (http://guides.rubyonrails.org/active_record_querying.html) would be very welcome as well.

Thanks in advance.

wicz · Accepted Answer · 2014-08-05T22:41:19.870

1

Try this:

def self.not_expired
  where("created_at + (expiration_in_days * 86400)::text::interval >= ?", Time.now)
end

UPDATE: Add references

You can learn more about date and time function here.

Given your case has a special requirement, which is the value of expiration_in_days is a column in the table, we cannot use created_at + interval expiration_in_days day. Instead, we need to type cast its value to interval. But you can't type cast straight to an integer, that's why we cast it to text first.

edited Aug 05 '14 at 22:41

answered Aug 05 '14 at 22:26

wicz

2,315
12
13

And tests are passing, that works. Thank you. I have absolutely no idea where I would have learned to do that, though. Any suggestions for resources on this? – steel Aug 05 '14 at 22:28
Hi @steel, I've updated the answer with some references. HTH! – wicz Aug 05 '14 at 22:41

score 0 · Answer 2 · answered Aug 05 '14 at 22:23

0

A + B > C is true if A <= C - B

So, instead of trying to add the expiration time to created_at, subtract the expiration time from Time.now.

def expiration_threshold
  Time.now - amount_of_time # I'm not sure what type to use
end

def self.not_expired
  where( "created_at > ? ", expiration_threshold )
end

I've always been a little stumped about what type Rails/Ruby will want when dealing with various dates/times, you may have to play around.

answered Aug 05 '14 at 22:23

Carl

993
8
14

The problem is the expiration threshold is different for every record. I'm not sure how this addresses that. – steel Aug 05 '14 at 22:27
Ah ha. Thats an issue for my solution. – Carl Aug 05 '14 at 22:39

score 0 · Answer 3 · answered Aug 05 '14 at 22:33

0

I'm not sure if this would work but try something like this

def self.not_expired
  where("TIMESTAMP created_at + INTERVAL '1 day' * expiration_in_days >= NOW()")
end

answered Aug 05 '14 at 22:33

Mohammad AbuShady

40,884
11
78
89

Rails query: Compare calculation of two attribute values

3 Answers3