Math with datetime in SQL query in rails

Question

So I have an table of phone_numbers in Rails 3.2, and I would like to be able to make a query such as the following:

PhoneNumber.where("last_called_at - #{Time.now} > call_spacing * 60")

where last_called_at is a datetime and call_spacing is an integer, representing the number of minutes between calls.

I have tried the above, I have also tried using

PhoneNumber.where("datediff(mi, last_called_at, #{Time.now}) > call_spacing")

If anyone could help me make this work, or even recommend a current, up-to-date rails SQL gem, I would be very grateful.

Edit: So the best way to make this work for me was to instead add a ready_at column to my database, and update that whenever either call_spacing or last_called_at was updated, using the before_save method. This is probably the most semantic way to approach this problem in rails.

Pawel · Accepted Answer · 2013-07-07 23:22:01Z

1

You have to use quotes around #{Time.now}. To make your first query work you may use TIME_TO_SEC() function:

PhoneNumber.where("(TIME_TO_SEC('#{Time.now}') - TIME_TO_SEC(last_called_at)) > (call_spacing * 60)")

Here is my way to do this:

PhoneNumber.where("last_called_at > '#{Time.zone.now.utc - (call_spacing * 60)}'")

also take a look at this article to be aware of how to play with timezones:

http://danilenko.org/2012/7/6/rails_timezones/

edited Jul 7, 2013 at 23:22

answered Jul 7, 2013 at 18:41

Pawel

9446 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OneChillDude Over a year ago

Ok, I think this is on the right track, but part of the problem with this query is that call_spacing is not a local variable, its a database column.

Pawel Over a year ago

As long as you want to compare it with call_spacing which is an integer value you may use TIME_TO_SEC for those dates and then make calculations using seconds. Please look again at first query in my answer. It should be a good way to go. Is there somethig more to do than specified problem?

Collectives™ on Stack Overflow

Math with datetime in SQL query in rails

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related