CODEWITHSUNDEEP
ruby-on-railspostgresql
croceldon
croceldon

Reputation: 4595

What is the easiest way to query for a date in a range using Rails 4 and postgres?

I'm trying to select all records with a created_by attribute in a date range:

@start = params[:start].to_datetime
@end = params[:end].to_datetime
@registrations = Registration.where("created_at >= #{ @start } and created_at <= #{ @end }")

But I get an error from postgres that says:

PG::Error: ERROR:  syntax error at or near "T00"
LINE 1: ...M "registrations"  WHERE (created_at >= 2013-06-01T00:00:00+...

I've been searching on this one, but Google isn't coming through this time...

Upvotes: 4

Views: 3633

Answers (4)

Daniel
Daniel

Reputation: 4173

@start = params[:start].to_datetime
@end = params[:end].to_datetime
@registrations = Registration.where(created_at: @start..@end)

Upvotes: 0

kiddorails
kiddorails

Reputation: 13014

The best way, in my opinion will be to use range in where clause as:

Registration.where(created_at: @start..@end)

This query will not be prone to SQL injections either, since @start and @end are already typecasted in DateTime in your query.

Upvotes: 13

usha
usha

Reputation: 29349

@start = params[:start].to_datetime
@end = params[:end].to_datetime
@registrations = Registration.where("created_at >= '#{@start}' and created_at <= '#{@end}'")

Upvotes: -1

Chris Peters
Chris Peters

Reputation: 18090

Try binding the query parameters like this:

@registrations = Registration.where "created_at >= ? AND created_at <= ?",
                                    params[:start].to_datetime,
                                    params[:end].to_datetime

This allows you to bind the parameters in a way that helps prevent SQL injection attacks.

Upvotes: 2

Related Questions