ruby query between two date parameters

Question

I have a model called Result. I need to create a variable that returns all results that come in between a certain date range. On the Result model there is a field called date, and on a form I am capturing a start and end date as parameters and passing them back to the controller.

so if the user enters '01/01/2014' in the startdate parameter and '01/01/2015 in the parameters I need to return all results where the date is between this range.

When the user pressers a "filter" button the parameters end up being captured as variables startdate and enddate

I tried this but it doesn't seem to work

@results = Result.where("date >= ? and date <= ?", startdate, enddate")

I then looked at the resulting SQL and thought it needed to be this

@results = Result.where("date >= ? and date <= ?", '#{startdate)', '#{enddate}')

Any ideas?

Thanks

Are your Date input formats are always fixed ? – Arup Rakshit Jun 05 '14 at 17:01 — Arup Rakshit, Jun 05 '14 at 17:01

score 2 · Answer 1 · edited May 23 '17 at 10:32

2

As @MrYoshiji's also suggests, one thing you should take care in such a scenario is that both your startdate and enddate are either Date, DateTime or Time objects. Else, the comparison will fail.

1.) A shortcut would be to apply a datepicker in to your input tag. You can specify the format in the initializer for the datepicker.

2.) Use a date/time tag rails form helper.

3.) Use a regex to ensure a correct format.

And when it passes all your validations, then do:

@results = Result.where(date: Date.parse(startdate).beginning_of_day..Date.parse(enddate).end_of_day)

Also you can add a rescue at the end to ensure it does not go to a 500 page.

At the end of the query add a rescue Result.all (in case of ALL-ELSE-FAILED)

Yes, and make sure you get the timezones right. Default for postgresql is UTC.

edited May 23 '17 at 10:32

Community

1
1

answered Jun 05 '14 at 16:56

manu29.d

1,538
1
11
15

1

That is risky, if the `Date.parse` fails, it will raise an error – MrYoshiji Jun 05 '14 at 16:56
He said his parameters are of the form '01/01/2014'. So, I thought this would work. – manu29.d Jun 05 '14 at 16:58
`He said his parameters` Since it is a parameter, it means that every user sends it: "never trust user's input". I think this `start_date = Date.parse(startdate) rescue Date.current` is the minimum, eventually rescue with an error message saying "date can't be parsed" – MrYoshiji Jun 05 '14 at 16:59

score 1 · Accepted Answer · answered Jun 06 '14 at 10:34

Thanks to everyone for the advice. This was my solution in the end.

Firstly, ensure only valid dates can be entered in the form by using datepicker, which looks like this...

  $(function(){
    $('#startdate').datepicker({
      changeYear: true,
      dateFormat:"dd/mm/yy",
      defaultDate: "#{Date.today.day}/#{Date.today.month}/#{Date.today.year}",
      change: function() {
        var isoDate = this.getValue('yyyy-mm-dd');
        $(this).attr('data-value', isoDate);
      }
    });
$('#enddate').datepicker({
  changeYear: true,
  dateFormat:"dd/mm/yy",
  defaultDate: "#{Date.today.day}/#{Date.today.month}/#{Date.today.year}",
  change: function() {
    var isoDate = this.getValue('yyyy-mm-dd');
    $(this).attr('data-value', isoDate);
    }
  });
});


%form{:action => employee_results_path(@employee), :class => 'navbar-search pull-left'} 
  .field
    %input{:type =>'text', :placeholder => params[:startdate] != nil ? params[:startdate] : "from date", :id => 'startdate', :name => "startdate", :style => 'width: 100px'}
    %input{:type =>'text', :placeholder => params[:startdate] != nil ? params[:enddate] : "to date", :id => 'enddate', :name => "enddate", :style => 'width: 100px'}

then, in the controller capture the parameters as variables and Parse them as dates

sdate = Date.parse(params[:startdate])
edate = Date.parse(params[:enddate])

Then use the helpful solution I recieved in this question

@results = Result.where(:date => sdate..edate)

which worked a treat. Thanks to everyone to helped me learn!

Arup Rakshit · Answer 3 · 2014-06-05T17:04:51.630

0

You can write as

@results = Result.where(:date => startdate..enddate)

But before use the query, startdate and enddate must need to convert to a Date object.

edited Jun 05 '14 at 17:04

answered Jun 05 '14 at 16:54

Arup Rakshit

116,827
30
260
317

That was the key for me in answering this question, thank you. – Gareth Burrows Jun 06 '14 at 10:38

ruby query between two date parameters

3 Answers3

Linked