Rails / Rails3 SQL查询，“LIKE”不适合日期时间吗？

Are rails SQL queries actually compatible with datetimes?

rails SQL查询实际上是否与日期时间兼容?

For example, if I do:

例如,如果我这样做:

User.where("email LIKE ?", "%.com%")

I would expect to (and do) get back a list of all users with a ".com" email address.

我希望(并且确实)使用“.com”电子邮件地址返回所有用户的列表。

However, if I do:

但是,如果我这样做:

User.where("created_at LIKE ?","%2011%")

I get back nothing, even though I can see that there are users created this year. In point of fact, sometimes i get gibberish back (for example, I might get stuff (with seemingly nothing in common) back for %20%, but nothing back for %30% even though I know there are dates with a 30 in them), which makes me think the "LIKE" is applying to something I don't see/care about (maybe some sort of ID?)

我什么都没有回来,尽管我可以看到今年有用户创建。事实上,有时我会得到胡言乱语(例如,我可能得到的东西(似乎没有任何共同点)回到%20%,但没有回到%30%,即使我知道有30个在他们的日期),这让我觉得“喜欢”适用于我看不到/不关心的事情(也许某种ID?)

Are LIKES for string only? Is there some way I can do something similar for date times, in rails (I'm sure there's some way to do it in mysql, but can rails also handle it?)

LIKES只用于字符串吗?有没有什么方法可以在日期中做类似的东西,在rails中(我确定在mysql中有一些方法可以做到这一点,但是rails也可以处理它吗?)

Specifically, I want to use this in a scope, similar to:

具体来说,我想在范围内使用它,类似于:

 scope :by_date, lambda {|date| where("created_at LIKE ?",date)}

2 个解决方案

#1

IIRC, datetime values are stored as integers. LIKE operator works on strings.

IIRC,datetime值存储为整数。 LIKE运算符用于字符串。

You can try using CONVERT() or CAST() or even CONCAT().

您可以尝试使用CONVERT()或CAST()甚至CONCAT()。

Something like:

 CONVERT(created_at, VARCHAR(20))

#2

dt = Date.new(2011,1,1)
User.where(['created_at >= ? AND created_at < ?',dt,dt+1.year])

produces

SELECT "users".* FROM "users" WHERE (created_at >= '2011-01-01' AND created_at < '2012-01-01')

Which is a better way to handle datetimes IMHO

哪个是处理日期时间的更好方法恕我直言

#1