# Comparing date problem

**URL:** <https://www.ruby-forum.com/t/comparing-date-problem/196882>\
**Category:** Ruby\
**Created:** [January 19, 2009, 4:18pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882 "2009-01-19T16:18:42Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [January 19, 2009, 4:18pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/1 "2009-01-19T16:18:42Z")

</div>

I have a set of dates in my database and would like to only get the  
latest one or the one closest to the current date. I’m trying to come up  
with a way of doing it but had no luck. If any one could help I would  
appreciate it.

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [January 19, 2009, 4:24pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/2 "2009-01-19T16:24:19Z")

</div>

Sam G. wrote:

> I have a set of dates in my database and would like to only get the  
> latest one or the one closest to the current date. I’m trying to come up  
> with a way of doing it but had no luck. If any one could help I would  
> appreciate it.

SQL problem? Show the SQL you’ve written already. Consider using the  
max() operator. There are some good examples at  
[http://dev.mysql.com/doc/refman/5.0/en/examples.html](http://dev.mysql.com/doc/refman/5.0/en/examples.html) (which may be  
useful even if you’re not using MySQL)

ORM problem? Solution will depend on which ORM you are using (e.g.  
ActiveRecord, Datamapper, Sequel, Og, …).

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [January 19, 2009, 4:38pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/3 "2009-01-19T16:38:06Z")

</div>

Brian C. wrote:

> Sam G. wrote:
> 
> > I have a set of dates in my database and would like to only get the  
> > latest one or the one closest to the current date. I’m trying to come up  
> > with a way of doing it but had no luck. If any one could help I would  
> > appreciate it.
> 
> SQL problem? Show the SQL you’ve written already. Consider using the  
> max() operator. There are some good examples at  
> [http://dev.mysql.com/doc/refman/5.0/en/examples.html](http://dev.mysql.com/doc/refman/5.0/en/examples.html) (which may be  
> useful even if you’re not using MySQL)
> 
> ORM problem? Solution will depend on which ORM you are using (e.g.  
> ActiveRecord, Datamapper, Sequel, Og, …).

I’m using thinking sphinx search engine. an sql syntax in thinking  
sphinx looks like this

@allRooms = Room.search :conditions =\> {:created\_at =\> here is where I  
should have a statement}

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [April 21, 2009, 10:31pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/4 "2009-04-21T22:31:52Z")

</div>

I’m having a similar issue, but can’t seem to figure out what the  
solution is. Here’s my code:

now = Time.now  
@events = Event.find(:all, :conditions =\> ["event\_date \> " + now.to\_s,  
{:venue\_id =\> @venue.id}])

Basically, I want to pull all the records from the Events table where  
the event\_date has not passed. The code above is generating the  
following error:

“Mysql::Error: You have an error in your SQL syntax; check the manual  
that corresponds to your MySQL server version for the right syntax to  
use near ‘Apr 21 16:31:05 -0400 2009)’ at line 1: SELECT \* FROM `events`  
WHERE (event\_date \> Tue Apr 21 16:31:05 -0400 2009)”

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [January 19, 2009, 10:12pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/5 "2009-01-19T22:12:25Z")

</div>

Sam G. wrote:

> I’m using thinking sphinx search engine.

Sphinx is a new one on me - but looking at the project page, it looks  
like it needs either a MySQL or PostgreSQL database sitting under it.

> an sql syntax in thinking  
> sphinx looks like this
> 
> @allRooms = Room.search :conditions =\> {:created\_at =\> here is where I  
> should have a statement}

I think you mean Room.find here, if this is ActiveRecord, in order to  
do a SQL “select” on a table in the underlying database. If I’m right,  
this is nothing to do with Sphinx’s free-text searching. So it would be  
something like

Room.find(:all, :conditions =\> { … })

The SQL you need is at the link I gave before: see “The Row Holding the  
Maximum of a Certain Column”. All you need to do is to look at your  
ActiveRecord docs to formulate a suitable query. Untested:

–SELECT article, dealer, price  
–FROM shop  
–WHERE price=(SELECT MAX(price) FROM shop);

Shop.find(:first, :conditions =\> “price = SELECT MAX(price) FROM shop”)

–SELECT article, dealer, price  
–FROM shop  
–ORDER BY price DESC  
–LIMIT 1;

Shop.find(:first, :order =\> “price desc”)

ActiveRecord docs are at [http://ar.rubyonrails.com/](http://ar.rubyonrails.com/) and there are also  
some very good Rails books you can buy with good ActiveRecord coverage.

For further help on ActiveRecord you’d probably get best results going  
to a Rails-specific mailing list.

Regards,

Brian.

P.S. I came across a blog post on Sphinx at

> **[Using Sphinx search engine in Ruby on Rails | Dmytro Shteflyuk's Home](https://kpumuk.info/ruby-on-rails/using-sphinx-search-engine-in-ruby-on-rails/)**
>
> Almost all Web-applications needs data search logic and really often this logic should have full-text search capabilities. If you are using MySQL database, you can use its FULLTEXT search, but it's not efficient when you have a large amout of data....

which may be helpful for when you get to the free-text searching.

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [April 22, 2009, 1:21am UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/6 "2009-04-22T01:21:02Z")

</div>

On Apr 21, 2009, at 4:43 PM, Taneal Bhandari wrote:

> ## bit, but it doesn’t like any combination that I’ve tried. Any thoughts?

:conditions =\> [ 'event\_date \> ? OR (event\_date = ? AND event\_time

> ?)',  
> now.to\_date, now.to\_date, now.strftime(“%H:%M:%S”) ]

depending on what the format of event\_time actually is, of course.

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [April 21, 2009, 10:43pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/7 "2009-04-21T22:43:03Z")

</div>

Taneal Bhandari wrote:

> now = Time.now  
> @events = Event.find(:all, :conditions =\> ["event\_date \> " + now.to\_s,  
> {:venue\_id =\> @venue.id}])

Actually, I just realized that I also have to take into consideration  
“event\_time”. Our table has 2 fields event\_date and event\_time, and I  
only want to pull back records of events where both the event\_date and  
event\_time have not passed. I’ve been playing around with it for a bit,  
but it doesn’t like any combination that I’ve tried. Any thoughts?

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [April 22, 2009, 4:48pm UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/8 "2009-04-22T16:48:07Z")

</div>

Rob B. wrote:

> :conditions =\> [ 'event\_date \> ? OR (event\_date = ? AND event\_time
> 
> > ?)',  
> > now.to\_date, now.to\_date, now.strftime(“%H:%M:%S”) ]  
> > -Rob
> 
> Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
> removed\_email\_address@domain.invalid

Just tested this out and it worked like a charm!!! Much thanks!

-tan

---

<div class="post-metadata">

**Author:** ![staypufd](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ecd19e/32.png) [@staypufd](https://www.ruby-forum.com/u/staypufd)\
**Post date:** [April 22, 2009, 2:22am UTC](https://www.ruby-forum.com/t/comparing-date-problem/196882/9 "2009-04-22T02:22:25Z")

</div>

Rob B. wrote:

> :conditions =\> [ 'event\_date \> ? OR (event\_date = ? AND event\_time
> 
> > ?)',  
> > now.to\_date, now.to\_date, now.strftime(“%H:%M:%S”) ]
> 
> depending on what the format of event\_time actually is, of course.
> 
> -Rob
> 
> Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
> removed\_email\_address@domain.invalid

Cool! Thanks! I’m actually home now so will give it a go in the  
morning. But it looks like it should work. I believe that event\_time  
is the same format as what you have in your code. Thanks again! Will  
update post in morning!