Forum: Ruby on Rails Search between dates won't work properly

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
38643ec0dd1506548e9f9fc7b4dcc22f?d=identicon&s=25 Gabi Ge (gabi)
on 2009-02-09 18:29
I'd like to search for invoices in the database, having the date range
set (from, to). The problem is that invoices whose issue dates match the
'from' or 'to' dates will not show up in results.

For example:
Created an invoice today ("2009-02-09"), and I'm searching by issue_date
between from and to, these are the params:

 "filter"=>{"from"=>"2009-02-09", "to"=>"2009-02-09"}

In the model I have the sql conditions, as well as a scope, looks like
this:

 self.cond = Array.new
 self.cond << ["invoices.issue_date >= ?", from] unless from.blank?
 self.cond << ["invoices.issue_date <= ?", to] unless to.blank?
 result = join_conditions
---
 named_scope :filter, lambda { |invoice_filter|
    { :conditions => invoice_filter.sql_conditions }}

The scope gets a filter object that has both 'from' and 'to' fields set.
If I set the 'to' field to tomorrow's date, then the invoice will
appear, but I don't understand why it wouldn't work as is since there
are '<=' & '>=' comparisons in conditions.

Any help or suggestion would be appreciated!
0d56133042e4814c1b2255ea655f2361?d=identicon&s=25 Jeff Burlysystems (jburly)
on 2009-02-09 19:00
(Received via mailing list)
How about:

...
require 'time'
...
invoices = Invoice.find(:all, :conditions=>['issue_date is not null
and (issue_date >= ? or issue_date <= ?)', Time.parse('2009-02-09
00:00:00'), Time.parse('2009-02-09 23:59:59')] :order=>'issue_date')
...

Jeff
Af2ce6689213fdb78913a9662b18da6b?d=identicon&s=25 Rick (Guest)
on 2009-02-09 20:16
(Received via mailing list)
things = Thing.find_all_by_field('start_at'..'end_at')
38643ec0dd1506548e9f9fc7b4dcc22f?d=identicon&s=25 Gabi Ge (gabi)
on 2009-02-09 20:29
Jeff Burlysystems wrote:
> How about:
>
> ...
> require 'time'
> ...
> invoices = Invoice.find(:all, :conditions=>['issue_date is not null
> and (issue_date >= ? or issue_date <= ?)', Time.parse('2009-02-09
> 00:00:00'), Time.parse('2009-02-09 23:59:59')] :order=>'issue_date')
> ...
>
> Jeff

Thanks for your reply, and your help! It gave me a hint and solved the
problem like this:

 self.cond << ["invoices.issue_date > ?", from - 1] unless from.blank?
 self.cond << ["invoices.issue_date < ?", to + 1] unless to.blank?

because issue_date is a datetime, and generating an invoice means saving
both date and time of the day. This way the search will have the same
results as in your example.

Once again, thank you for your help!
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-02-10 01:07
(Received via mailing list)
On 9 Feb 2009, at 17:29, Gabi Ge wrote:

>
> "filter"=>{"from"=>"2009-02-09", "to"=>"2009-02-09"}

Is the issue date a date column or a datetime column ?
if you have a datetime column containing  2009-02-09 14:00 and compare
it to 2009-02-09 then the 2009-02-09 is turned into 2009-02-09 00:00
which can mess things up for you.

Fred
3131fcea0a711e5ad89c8d49cc9253b4?d=identicon&s=25 Julian Leviston (Guest)
on 2009-02-10 05:04
(Received via mailing list)
I didn't know you could pass an array of arrays!

Blog: http://random8.zenunit.com/
Learn rails: http://sensei.zenunit.com/

On 10/02/2009, at 6:29 AM, Gabi Ge <rails-mailing-list@andreas-s.net>
This topic is locked and can not be replied to.