How to group by day and sum a calculated property with GroupDate?

Question

I'm using chartkick to render charts in my rails app. And I'd like to use groupdate to query the data for the last 7 days getting 0 if a day is not present.

I know I can use it like this:

project.time_entries.group_by_day(:created_at, last: 7, format: "%a\n%d %b").count

However, I don't want to get the count of the records found, I want instead to get the sum of a calculated field:

class TimeEntry < ActiveRecord::Base
  def duration_sec
    (self.time.end - self.time.begin).to_i
  end
end

so if there were 4 records on 25th March instead of getting:

{"20 Mar"=>0, "21 Mar"=>0, "22 Mar"=>0, "23 Mar"=>0, "24 Mar"=>0, "25 Mar"=>4, "26 Mar"=>0}

I'd like to get the total sum of duration_sec for that day. So I want to accomplish something like this with DateGroup:

project.time_entries.group_by{ |a| a.created_at.to_date.to_s }.map { |k, v| { k => v.sum(&:duration_sec) } }

I also tried something like this:

project.time_entries.group_by_day(:created_at, last: 7, format: "%a\n%d %b").sum(&:duration_sec)

but I get an argument error exception.

score 0 · Answer 1 · answered Apr 05 '16 at 07:56

0

As far as I know, ActiveRecord::Calculations#sum cannot call custom methods. In the meantime, this might be a workaround..

TimeEntry.unscoped.select(:duration_sec)
  .from(project.time_entries.select('time_end - time_begin AS durations_sec, created_at'))
  .group_by_day(:created_at, last: 7, format: "%a\n%d %b").sum(:duration_sec)

answered Apr 05 '16 at 07:56

cozyconemotel

1,121
2
10
22

time is a tsrange from postgresql, i guess that's why this isn't working for me, do you know how to get the `begin` and `end` values from a range type in postgres? – Carlos Martinez Apr 18 '16 at 15:08
Ok, i'm using `upper(time) - lower(time)` and it seems to work. however, the format of the value is a string like `hh:mm:ss` and I actually need it in float values – Carlos Martinez Apr 18 '16 at 15:22

score 0 · Answer 2 · answered Apr 18 '16 at 20:37

from @cozyconemotel answer, I managed to get what I needed like this:

TimeEntry.from(time_entries.select('(EXTRACT(EPOCH FROM (UPPER(time) - LOWER(time)))) AS duration, created_at')).group_by_day(:created_at, last: 7, format: "%a\n%d %b").sum(:duration)

This extracts the timestamp in seconds and not in a string format, which is what I needed

How to group by day and sum a calculated property with GroupDate?

2 Answers2