66twlg
Last Updated: February 25, 2016
·
391
· rafaelcgo
2014 06 28 chopada med pesqueiro squared

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.

Say Thanks
Respond

2 Responses
Add your response

23219
B8f8f573e852c25d5750027fdb5ca38f

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

over 1 year ago ·
23223
2014 06 28 chopada med pesqueiro squared

@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.

over 1 year ago ·