It depends on the database. In general, when you manipulate the left-hand side of a WHERE clause, the planner punts, but I know some databases have been adding sophistication to handle some common (deterministic) functions.
I read MS SQL Server (since v2008) will use an index on a DateTime field for "CONVERT(Date, whatever_column)" clauses. To my knowledge, PostgreSQL and MySQL do not. PostgreSQL does support indexes on expressions<http://www.postgresql.org/docs/9.1/static/indexes-expressional.html>though. You could always split that DateTime into separate Date and Time/Int columns, or add another column for just the date portion - depending on the needs of your application. I'm curious about this, if anybody has deeper or more up-to-date knowledge. I haven't looked into this in a while. -Al On Thu, Feb 6, 2014 at 8:40 AM, Will Bryant <[email protected]> wrote: > Yup, AFAIK the most portable syntax is where("CAST(created_at AS DATE) = > ?", Date.yesterday). > > You'll also see database-specific syntax like where("created_at::date = > ?", Date.yesterday) which is more readable (that one is for postgresql). > > I'd be interested to hear if anyone knows whether most databases know how > to use their indexes to answer queries involving downcasts? I've never > gotten around to testing it. > > > On 7/02/2014, at 02:33 , Jonathan Lozinski <[email protected]> > wrote: > > Is there a better solution available where the developer can explicitly > request to cast the date time column to a date for the purposes of that > query. > > In the int vs float example, they might cast floats to ints with rounding > or floor values, and then the results would make sense (because it's what > the user requested) > > Sent from my iPhone > > On 6 Feb 2014, at 13:16, Will Bryant <[email protected]> wrote: > > This is almost a FAQ because the idea seems reasonable at first. > > But that isn't really how SQL works, nor data-handling operations in > normal programming languages. When you compare a type of a lower precision > to a type of a higher precision, they promote the value to the *higher* > precision, and then compare that. > > For example, if you have a floating point column called myval and one row > has the value 0.5, you could obviously use where(:myval => 0.0..1.0) to > find it using floating point numbers for the range. Now you *could* write > a query API that treats integer values X as covering the entire range from > X.0 to X.9999.., so that where(:myval => 0) would find the row that had > value 0.5 - but that would be a pretty damn weird API. where(:myval => 0) > and where(:myval => 0.0) would give different results! > > What you are suggesting is exactly the same idea, but instead of integers > you have dates, and instead of floats you have date-times. (These two > concepts should be a direct mapping in your head - dates are whole numbers > counted since some arbitrary start date (most of us use 1 Jan 0 CE, some > things use 1 Jan 1970 for unixy reasons - whatever); times are > arbitrary-precision subdivisions of the whole days. The precision you get > is even configurable per column in many databases.) > > No-one does this because it's inconsistent and although it would give the > result that you want in this situation, it produces completely > counterintuitive results in other situations. > > For example, if where(:created_at => Date.yesterday) includes some row R, > then you would also expect that where("created_at <= ?", Date.yesterday) > would also find row R and so would where("created_at > ?", Date.yesterday). > > Now think about how to make that work. When we see a Date value, how do > we know to treat it as 00:00:00 or 23:59:59.9999.. so that both of those > where ranges can work? > > It's not possible, and so to introduce a special case for the equality > case that does not also work for inequalities and more general parameter > substitution is going to lead to a lot of very confused programmers and > buggy code. > > So, like I say, SQL doesn't do this, and neither does any language binding > that I know of. There has to be one consistent rule to compare types of > different precisions, and the only sensible way to do this is to promote > the lower type to the higher precision rather than do the reverse. > > That means that a date being compared to a time is always equivalent to > 00:00:00 on the day, the same way that 42 being compared to a float is > equivalent to 42.0000. Making ActiveRecord do anything other would be > bizarre. > > Also, to ease your pain my suggestion would be that you don't write that > out manually if you do it a lot. Duckpunch a method that returns the range: > > class Date > def anytime > beginning_of_day..end_of_day > end > end > > User.where(:created_at => Date.yesterday.anytime) > > > On 6/02/2014, at 00:48 , Agis A. <[email protected]> wrote: > > When we want to find records created on a specific day, we have to do it > like this: > > > User.where(created_at: (Date.yesterday.beginning_of_ > day..Date.yesterday.end_of_day)).to_sql > => "SELECT `users`.* FROM `users` WHERE (`users`.`created_at` BETWEEN > '2014-01-28 00:00:00' AND '2014-01-28 23:59:59')" > > because if we don't supply a range the following will be queried: > > > User.where(created_at: Date.yesterday).to_sql > => "SELECT `users`.* FROM `users` WHERE `users`.`created_at` = > '2014-01-28'" > > What if we patched AR so when we query as I do in the 2nd case, it treats > it like it would do in the 1st query? > > So when I say: > > > User.where(created_at: Date.yesterday) > > It would bring me all the records that were created yesterday (from the > beginning to the end of date). > > Let me know if this makes sense and is feasible, and I can prepare a PR. > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/rubyonrails-core. > For more options, visit https://groups.google.com/groups/opt_out. > > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/rubyonrails-core. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/rubyonrails-core. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/rubyonrails-core. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/rubyonrails-core. For more options, visit https://groups.google.com/groups/opt_out.
