Rails ActiveRecord - how to fetch records between two dates

Question

I have two date columns - from_date and to_date in a database table.

Example:

from_date: 2012-09-10
to_date: 2012-09-30
today: 2012-09-13

I need to fetch all records, if today's date is between from_date and to_date. How do I do that with a SQL query?

If I have loaded the respective record, I can easily decide, if today's date is between from_date and to_date, but I don't know how to fetch those records straight from the database table.

I think this question may be helpful: [Rails ActiveRecord date between](http://stackoverflow.com/a/2381825/722783) — Mahmoud Gamal, Sep 13 '12 at 10:46

score 55 · Answer 1 · answered Sep 19 '14 at 08:57

55

Check the Rails guides on Range conditions:

Client.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)

That will produce the following SQL:

SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')

answered Sep 19 '14 at 08:57

Paulo Fidalgo

21,709
7
99
115

22

This doesn't actually answer the asked question. He wants to know when a date is between the value of two separate columns, this is where a column is between two dates. – Clive Evans Mar 24 '15 at 11:50
This doesn't actually answer the asked question. – Phuc Nguyen Minh Sep 12 '21 at 14:15

score 25 · Accepted Answer · answered Sep 13 '12 at 11:09

25

data = ModelName.where("today >= from_date AND today <= to_date")

answered Sep 13 '12 at 11:09

Salil

46,566
21
122
156

6

This answer is actually more general than Paulo's. Paulo's query assumes you have a field (`created_at`in his answer) that you expect to have in-between two dates. What if I want to see if today is between `start_date` and `end_date`? Can't do that with Paulo's answer. – Sebastialonso Aug 15 '16 at 18:43

Tim Krins · Answer 3 · 2018-12-05T11:51:19.507

11

A secure and easy way to do this would be:

Model.where(':date BETWEEN from_date AND to_date', date: Date.current)

edited Dec 05 '18 at 11:51

answered Dec 20 '17 at 16:16

Tim Krins

3,134
1
23
25

1

You can use `"?"` and no need to pass a hash containing the bind values (`Model.where('? BETWEEN from_date AND to_date', Date.current)`). Even shorter, you can rely in the functions your RDBMS offers; `Model.where('DATE_CURRENT BETWEEN from_date AND to_date')` (pg specific). – Sebastián Palma Apr 04 '20 at 18:23
3

I know... I prefer passing a hash because it is easier to understand and reads like a sentence. As for relying on the db for the current date, yeah you could, but then you are making the query in your database timezone, rather than in the timezone of your Rails application / user session. – Tim Krins Jun 17 '20 at 12:25

score 8 · Answer 4 · answered Nov 02 '16 at 15:11

8

you can use like this:

Data = Model.where("date(now()) between from_date and to_date")

answered Nov 02 '16 at 15:11

M.Prabha karan

697
6
17

score 5 · Answer 5 · answered Sep 13 '12 at 10:54

5

data = ModelName.find(:all, :conditions => "today >= from_date and today <= to_date")

answered Sep 13 '12 at 10:54

Ganesh Kunwar

2,643
2
20
36

score 3 · Answer 6 · answered Oct 03 '16 at 08:38

3

This should do the trick.

today = Date.today

data = ModelName.where("from_date <= ? AND to_date >= ?", today, today)

answered Oct 03 '16 at 08:38

rebbailey

714
1
7
16

score 1 · Answer 7 · answered Feb 05 '15 at 13:04

You could use below gem to find the records between dates,

This gem quite easy to use and more clear By star am using this gem and the API more clear and documentation also well explained.

ModelName.between_times(Time.zone.now - 3.hours,  # all posts in last 3 hours
                  Time.zone.now)

Here you could pass our field also ModelName.by_month("January", field: :updated_at)

Please see the documentation and try it.

score 0 · Answer 8 · answered Feb 16 '17 at 16:13

Ok, after a long search in google looking for a "rails way" to do a BETWEEN with two date fields and a variable, the most approximate solution I found is creating your own scope to do that.

in your ApplicationRecord write:

class ApplicationRecord < ActiveRecord::Base

  scope :between_fields, -> (value, initial_date, final_date) { 
    where "('#{value}' BETWEEN #{initial_date} AND #{final_date})"
   }

end

So now, wherever you need to do a between you can do:

@clients = Client.between_fields(params[:date], :start_date, :final_date)
# SELECT * FROM clients WHERE ('2017-02-16 00:00:00' BETWEEN start_date AND final_date)

You can combine it with others "where" to do your query as specific as you need.

A scope is a good way to do this, but using string interpolation is a bad idea for security. You should not be using the ` #{string}` syntax inside queries, especially when you are passing straight from params! — Tim Krins, Dec 20 '17 at 16:09
This implies a single column to be asked for, whereas the question is clearly stating two columns; `from_date` and `to_date`. — Sebastián Palma, Apr 04 '20 at 18:20

Rails ActiveRecord - how to fetch records between two dates

8 Answers8

Linked