El viernes, 25 de marzo de 2016, 18:24:19 (UTC), Jeremy Evans escribió:
> On Friday, March 25, 2016 at 10:57:00 AM UTC-7, Kaito Michishige wrote:
>>
>>
>>
>> For many years Sequel has supported this:
>>>
>>> where(Sequel.extract(:month, :date)=>d.month, Sequel.extract(:year,
>>> :date)=>d.year)
>>>
>>
>> This seems to generate the same SQL as what I posted above.
>>
>> Just for convenience, here's the generated SQL:
>>
>> WHERE ((extract(month FROM "date") = 3) AND (extract(year FROM "date") =
>> 2016))"
>>
>>
>>> However, in your example you are probably better off doing:
>>>
>>> start = Date.new(d.year, d.month)
>>> finish = start >> 1
>>> where(:date=>start...finish)
>>>
>>> as that is more likely to use an index.
>>>
>>
>> As far as I can tell, this would work as well. Here's the generated SQL:
>>
>> WHERE (("date" >= '2016-03-01') AND ("date" < '2016-04-01'))"
>>
>> Why does the first option not use an index, assuming one is present on
>> the column?
>>
>
> The query planner would need to have knowledge of extract for that to
> work, and I'm guessing it doesn't. Note that you can get the first type of
> query to use an index on PostgreSQL, but you'll have to create an index for
> it:
>
> add_index [Sequel.extract(:year, :date), Sequel.extract(:month, :date)]
>
> There's really no advantage to doing so, though, if you can just use the
> second query.
>
> Thanks,
> Jeremy
>
Thanks a lot, Jeremy!
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" 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 https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
