How to NOT query ActiveRecord datetime
I was having some trouble querying dates on a Rails project. Let's see some tips...
WRONG:
User.where("created_at >= '#{date_start}'")
.where("created_at <= '#{date_end}'")Produces:
"SELECT \"users\".* FROM \"users\" WHERE (created_at >= '2015-08-11 00:00:00 -0300') AND (created_at <= '2015-08-11 23:59:59 -0300')"
And Postgresql won't parse the date the right way.
RIGHT:
User.where("created_at >= ?", date_start)
.where("created_at <= ?", date_end)Produces:
"SELECT \"users\".* FROM \"users\" WHERE (created_at >= '2015-08-11 03:00:00.000000') AND (created_at <= '2015-08-12 02:59:59.999999')"
Ok case...
BETTER:
User.where(created_at: (date_start..date_end))Produces:
"SELECT \"users\".* FROM \"users\" WHERE (\"users\".\"created_at\" BETWEEN '2015-08-11 03:00:00.000000' AND '2015-08-12 02:59:59.999999')"
This is the preferred method because it "namescopes" the attribute with the table_name.
Written by Rafael Oliveira
Related protips
2 Responses
The first one is an insane way to do queries in Rails. Never interpolate strings directly in SQL statements, it's a huge security hole!
Nice to know about the third option
@zinkkrysty yeap. This query was made on a "admin" controller, just for some data viewing, so we accepted the commit. Anyways, still not a good a pick.
