On Wednesday, June 20, 2012 1:51:04 AM UTC-7, Stefan Rohlfing wrote:
>
> You are right, testing again I get the same output as you.
> Here is a self contained example: https://gist.github.com/2958805
>
query1 fails on both SQLite and PostgreSQL because you didn't add 1 to the
final date. You need to use:
filter(:created_at => (Date.today - number_of_days) .. (Date.today + 1))
query2 fails on PostgreSQL for the same reason, you need to use:
filter{(created_at >= Sequel::CURRENT_DATE - number_of_days) &
(created_at <= Sequel::CURRENT_DATE + 1)}
As I explained earlier, query2 cannot work on SQLite because it requires
specialized functions to do date/time arithmetic.
query3 works on SQLite using those functions, but fails on PostgreSQL since
it doesn't have those functions.
So if you don't mind doing the date/time arithmetic in ruby, that is
portable to both SQLite and PostgreSQL. Otherwise, if you want
portability, you'll either have to branch:
ds = DB[:visits].group_and_count(:created_at).
filter(:link_short => short).
ds = if DB.database_type == :sqlite
ds.filter{(created_at >= date(Sequel::CURRENT_DATE, "'-?
days'".lit(number_of_days.to_i))) &
(created_at <= date(Sequel::CURRENT_DATE,'+1
day'))}
else
ds.filter{(created_at >= Sequel::CURRENT_DATE - number_of_days) &
(created_at <= Sequel::CURRENT_DATE + 1)}
end
Or build your own abstraction layer.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/KncUc0Gy1woJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
