How to write complex Postgresql query with date_trunc, sum, group by and generate_series?

Question

PostgreSQL

There is a table table1 with the following schema:

create_table "table1", force: true do |t|
    t.datetime "time"
    t.string   "duration"
    t.string   "type"
    t.integer  "device_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "table1", ["device_id"], name: "index_table1_on_device_id", using: :btree
  add_index "table1", ["time"], name: "index_table1_on_time", using: :btree

it contains the response time of the sensor, the type of operation and the duration between the current and previous operation.
It is necessary to get a series generated by minutes, even if there are no time values ​​for a certain time. Plus, group the type and duration values ​​to get a collection at the output:

{"minute"=>"2015-01-16 17:16:00", "duration_norm"=>182.0, "duration_idle"=>80.0, "norm"=>6, "idle"=>2},
{"minute"=>"2015-01-16 17:17:00", "duration_norm"=>282.0, "duration_idle"=>180.0, "norm"=>10, "idle"=>4},
...
{"minute"=>"2015-01-16 17:18:00", "duration_norm"=>102.0, "duration_idle"=>40.0, "norm"=>4, "idle"=>1}

All I could get out of myself was this:

SELECT date_trunc('minute', clamps.time) AS minute, (case when type = 'norm' then duration end) as type_norm, (case when type = 'idle' then duration end) as type_idle FROM clamps GROUP BY minute,type,duration ORDER BY minute ASC

Answer 1

I didn’t quite understand why "duration" is of type "string", but if there are numeric values, then I think something like this should give the desired result:

SELECT tm "minute",
  sum(CASE WHEN type = 'norm' then duration::real ELSE 0 END) "duration_norm",
  sum(CASE WHEN type = 'idle' then duration::real ELSE 0 END) "duration_idle",
  sum(CASE WHEN type = 'norm' then 1 ELSE 0 END) "norm",
  sum(CASE WHEN type = 'idle' then 1 ELSE 0 END) "idle"
  
FROM generate_series('2015-01-16 17:16:00'::timestamp, '2015-01-16 18:18:00', '1 minute') tm
left join table1 t1 ON date_trunc('minute', t1.time) = tm
group by tm
ORDER BY tm