On Wednesday, June 20, 2012 8:52:28 PM UTC-7, Stefan Rohlfing wrote:
> Thanks for your corrections and recommendations. I already updated the
> gist <https://gist.github.com/2958805> accordingly.
>
> Now there is only one problem left: I want the rows to be grouped on the
> date only, not the date + the exact time.
> So, for example, with the current query I get the following output from my
> test program:
>
> [{:created_at=>2012-06-21 10:45:18 +0800, :count=>1},
> {:created_at=>2012-06-21 10:45:18 +0800, :count=>1}]
>
> However, what I need is this output:
>
> {:created_at=>2012-06-21, :count=>2}]
>
> In the original SQL query the rows are grouped according to the return
> value of the 'date' function:
>
> SELECT date(created_at) as date, count(*) as count
> FROM visits
> WHERE link_short = '#{short}' and
> created_at between CURRENT_DATE-#{nunber_of_days} and
> CURRENT_DATE+1
> GROUP BY date(created_at)
>
> Is there a way to accomplish the same in Sequel in a more or less
> database-agnostic way?
>
>
For SQLite, you can do:
select_group{date(created_at)}
For other databases, you should probably do:
select_group{created_at.cast(Date)}
The reason that doesn't work on SQLite is that SQLite doesn't have native
date or time types. I might change Sequel so that casting to Date uses the
date(function), since currently Sequel accepts the cast to date, but treats
it as a cast to integer (which if you've read the SQLite documentation, is
their expected behavior).
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/-/Q8uIyn_Q-KMJ.
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.
