# Subtract a field from the current time in MySQL?

**URL:** <https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858>\
**Category:** Rails\
**Created:** [July 25, 2006, 11:37pm UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858 "2006-07-25T23:37:21Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\
**Post date:** [July 25, 2006, 11:37pm UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858/1 "2006-07-25T23:37:21Z")

</div>

I have the following:

Table Products:  
With a column called “purchased\_at” of type “datetime”.

Products.find(:all, :conditions =\> ["? - purchased\_at \>= 120",  
Time.now])

Why doesn’t that work? What I’m trying to do is find records that were  
purchased less than or equal to 2 minutes ago.

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\
**Post date:** [July 26, 2006, 12:20am UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858/2 "2006-07-26T00:20:10Z")

</div>

Ben J. wrote:

> I have the following:
> 
> Table Products:  
> With a column called “purchased\_at” of type “datetime”.
> 
> Products.find(:all, :conditions =\> ["? - purchased\_at \>= 120",  
> Time.now])
> 
> Why doesn’t that work? What I’m trying to do is find records that were  
> purchased less than or equal to 2 minutes ago.
> 
> Thanks for your help.

I found the following to work:  
Products.find(:all, :conditions =\> “NOW() - purchased\_at \>= 120”

Just udating it for later searches.

---

<div class="post-metadata">

**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\
**Post date:** [July 26, 2006, 12:35am UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858/3 "2006-07-26T00:35:18Z")

</div>

> I found the following to work:  
> Products.find(:all, :conditions =\> “NOW() - purchased\_at \>= 120”
> 
> Just udating it for later searches.

SELECT something FROM tbl\_name  
WHERE DATE\_SUB(CURDATE(),INTERVAL 2 MINUTES) \<= date\_col;

so your find should be like this:

Products.find(:all, :conditions =\> “DATE\_SUB(CURDATE(),INTERVAL 2  
MINUTES) \>= purchased\_at”)

Interval can be something like 2 MINUTES.  
More info:  
[http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html)

regards,  
rolando.-

---

<div class="post-metadata">

**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\
**Post date:** [July 26, 2006, 9:05am UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858/4 "2006-07-26T09:05:52Z")

</div>

On 7/25/06, Ben J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Thanks for your help.

What about this?

Products.find(:all, :conditions =\> [“(? - purchased\_at) \>= 120”,  
Time.now])

Does this work? Should result in a query like this:

SELECT \* FROM products WHERE (the\_current\_datetime\_here - purchased\_at)

> = 120

Hope that helps.

---

<div class="post-metadata">

**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\
**Post date:** [July 27, 2006, 8:58am UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858/5 "2006-07-27T08:58:29Z")

</div>

Hello Ben,

> Table Products:  
> With a column called “purchased\_at” of type “datetime”.
> 
> Products.find(:all, :conditions =\> ["? - purchased\_at \>= 120",  
> Time.now])
> 
> Why doesn’t that work? What I’m trying to do is find records that were  
> purchased less than or equal to 2 minutes ago.

Try sth like :

Product.find(:all, :conditions =\> [ “purchased\_at \> ?”,  
2.minutes.ago.to\_s(:db) ])

```
-- Jean-François.
```

---

<div class="post-metadata">

**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\
**Post date:** [July 31, 2006, 11:52pm UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858/6 "2006-07-31T23:52:23Z")

</div>

Hi peepz,

I have quite a strange problem, when I visit my rails application in  
internet explorer sometimes it won’t complete the page and just give  
aerror. I saw that disabling external extensions fixes the problems but  
it won’t. Firefox just workes fine.

Mongrel doesn’t give a stackstrace only invalid argument error that why  
I listed the webrick error below…

It gives errors like:

127.0.0.1 - - [31/Jul/2006:23:47:42 W. Europe Standard Time] “GET  
/images/edit.gif?1154286828 HTTP/1.1” 304 0  
[2006-07-31 23:47:42] ERROR Errno::EINVAL: Invalid argument  
C:/Program Files/ruby/lib/ruby/1.8/webrick/httpresponse.rb:324:in  
`write' C:/Program Files/ruby/lib/ruby/1.8/webrick/httpresponse.rb:324:in`\_write\_data’  
C:/Program Files/ruby/lib/ruby/1.8/webrick/httpresponse.rb:180:in  
`send_header' C:/Program Files/ruby/lib/ruby/1.8/webrick/httpresponse.rb:103:in`send\_response’  
C:/Program Files/ruby/lib/ruby/1.8/webrick/httpserver.rb:79:in `run' C:/Program Files/ruby/lib/ruby/1.8/webrick/server.rb:173:in`start\_thread’  
C:/Program Files/ruby/lib/ruby/1.8/webrick/server.rb:162:in  
`start_thread' C:/Program Files/ruby/lib/ruby/1.8/webrick/server.rb:95:in`start’  
C:/Program Files/ruby/lib/ruby/1.8/webrick/server.rb:92:in `start' C:/Program Files/ruby/lib/ruby/1.8/webrick/server.rb:23:in`start’  
C:/Program Files/ruby/lib/ruby/1.8/webrick/server.rb:82:in `start’

./script/…/config/…/vendor/rails/railties/lib/webrick\_server.rb:67:in  
`dispatch’

./script/…/config/…/vendor/rails/railties/lib/commands/servers/webrick.rb:59  
C:/Program  
Files/ruby/lib/ruby/site\_ruby/1.8/rubygems/custom\_require.rb:27:in  
`require’

./script/…/config/…/vendor/rails/activesupport/lib/active\_support/dependencies.rb:147:in  
`require' ./script/../config/../vendor/rails/railties/lib/commands/server.rb:39 C:/Program Files/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in`require’  
./script/server:3  
-e:4

anyone know whats wrong?

Abdul

---

<div class="post-metadata">

**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\
**Post date:** [August 1, 2006, 12:55am UTC](https://www.ruby-forum.com/t/subtract-a-field-from-the-current-time-in-mysql/99858/7 "2006-08-01T00:55:35Z")

</div>

Strange, now I just get half generated pages in both internet explorer  
and firefox…