SQL adapter convert Date objects automatically to DateTime ranges.

rubyonrails-core
1

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.

2

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)

3

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)

4

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.

5

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