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.
