昨日の単純な例と違って実際は「3時間ずつ」などの集計が必要で、後から時間区分の変更があったり。でもPostgreSQLの範囲型があれば簡単・柔軟に対応できます。自分がよく使うクエリをテンプレ化してみました。


PostgreSQL 9.5.2で動作確認。範囲型が使える9.2以降なら同様に動くと思います。サンプルテーブルは昨日と同じで、下に再掲。

»date_trunc_example.tsv.zip (168kb)
create table date_trunc_example (ts timestamp, val numeric(9,2));       
copy date_trunc_example from '/date_trunc_example.tsv'
(format csv, delimiter E'\t', header true);

select * from date_trunc_example;
+----------------------------+---------+
|             ts             |   val   |
+----------------------------+---------+
| 2016-06-03 17:11:50.481619 | 79280.3 |
| 2016-06-03 17:26:40.752305 | 73544.0 |
| 2016-06-03 17:27:27.756976 | 73767.6 |
| 2016-06-03 17:29:07.201976 | 73270.7 |
| 2016-06-03 17:29:33.727976 | 70783.2 |
| 2016-06-03 17:29:51.441976 | 70286.2 |
| 2016-06-03 17:30:08.496976 | 72505.6 |
| 2016-06-03 17:30:17.230976 | 70834.9 |
| 2016-06-03 17:30:38.500976 | 70561.5 |
| 2016-06-03 17:30:51.788976 | 70109.1 |
...


↓ テンプレその1。データ先頭の日の12:00を起点に、データ末尾までの全ての「3時間ごとの行」を発生させて集計。データなし時も含めて検討する用に。起点と時間間隔の変更は、WITH句の二つ目のブロック(your_setting)を変えるだけで済みます。
with your_table (ts, val) as (
    select ts, val from date_trunc_example
), your_settings (start, intv) as (
    values(interval '12hour', interval '3hour')
), bounds (min, max) as (
    select min(ts), max(ts) from your_table
), ranges (tsr) as (
    select tsrange(ts, ts + intv)
    from bounds, your_settings, generate_series(
        date_trunc('day', min) + start, max, intv) as ts
)
select tsr, count(val) from ranges
left -- comment out to exclude times without data
join your_table on tsr @> ts
group by 1
order by 1;
+-----------------------------------------------+-------+
|                      tsr                      | count |
+-----------------------------------------------+-------+
| ["2016-06-03 12:00:00","2016-06-03 15:00:00") |     0 |
| ["2016-06-03 15:00:00","2016-06-03 18:00:00") |    70 |
| ["2016-06-03 18:00:00","2016-06-03 21:00:00") |     0 |
| ["2016-06-03 21:00:00","2016-06-04 00:00:00") |     0 |
| ["2016-06-04 00:00:00","2016-06-04 03:00:00") |     0 |
| ["2016-06-04 03:00:00","2016-06-04 06:00:00") |     0 |
| ["2016-06-04 06:00:00","2016-06-04 09:00:00") |     0 |
| ["2016-06-04 09:00:00","2016-06-04 12:00:00") |     0 |
| ["2016-06-04 12:00:00","2016-06-04 15:00:00") |     0 |
| ["2016-06-04 15:00:00","2016-06-04 18:00:00") |     0 |
| ["2016-06-04 18:00:00","2016-06-04 21:00:00") |     0 |
| ["2016-06-04 21:00:00","2016-06-05 00:00:00") |     0 |
| ["2016-06-05 00:00:00","2016-06-05 03:00:00") |   614 |
| ["2016-06-05 03:00:00","2016-06-05 06:00:00") |  1820 |
| ["2016-06-05 06:00:00","2016-06-05 09:00:00") |  1823 |
| ["2016-06-05 09:00:00","2016-06-05 12:00:00") |  1730 |
| ["2016-06-05 12:00:00","2016-06-05 15:00:00") |  1686 |
| ["2016-06-05 15:00:00","2016-06-05 18:00:00") |  1311 |
| ["2016-06-05 18:00:00","2016-06-05 21:00:00") |   416 |
| ["2016-06-05 21:00:00","2016-06-06 00:00:00") |   116 |
| ["2016-06-06 00:00:00","2016-06-06 03:00:00") |  1575 |
| ["2016-06-06 03:00:00","2016-06-06 06:00:00") |     0 |
| ["2016-06-06 06:00:00","2016-06-06 09:00:00") |     0 |
...


↓ テンプレその2。集計区分は同じで「データのある時」に限って見る場合。上のクエリと違うのは結合時のleftを消しただけです。
with your_table (ts, val) as (
    select ts, val from date_trunc_example
), your_settings (start, intv) as (
    values(interval '12hour', interval '3hour')
), bounds (min, max) as (
    select min(ts), max(ts) from your_table
), ranges (tsr) as (
    select tsrange(ts, ts + intv)
    from bounds, your_settings, generate_series(
        date_trunc('day', min) + start, max, intv) as ts
)
select tsr, count(val) from ranges
-- left -- comment out to exclude times without data
join your_table on tsr @> ts
group by 1
order by 1;
+-----------------------------------------------+-------+
|                      tsr                      | count |
+-----------------------------------------------+-------+
| ["2016-06-03 15:00:00","2016-06-03 18:00:00") |    70 |
| ["2016-06-05 00:00:00","2016-06-05 03:00:00") |   614 |
| ["2016-06-05 03:00:00","2016-06-05 06:00:00") |  1820 |
| ["2016-06-05 06:00:00","2016-06-05 09:00:00") |  1823 |
| ["2016-06-05 09:00:00","2016-06-05 12:00:00") |  1730 |
| ["2016-06-05 12:00:00","2016-06-05 15:00:00") |  1686 |
| ["2016-06-05 15:00:00","2016-06-05 18:00:00") |  1311 |
| ["2016-06-05 18:00:00","2016-06-05 21:00:00") |   416 |
| ["2016-06-05 21:00:00","2016-06-06 00:00:00") |   116 |
| ["2016-06-06 00:00:00","2016-06-06 03:00:00") |  1575 |
| ["2016-06-07 15:00:00","2016-06-07 18:00:00") |   147 |
| ["2016-06-08 09:00:00","2016-06-08 12:00:00") |    39 |
| ["2016-06-08 12:00:00","2016-06-08 15:00:00") |  1464 |
| ["2016-06-08 18:00:00","2016-06-08 21:00:00") |  1131 |
| ["2016-06-08 21:00:00","2016-06-09 00:00:00") |  1289 |
| ["2016-06-09 00:00:00","2016-06-09 03:00:00") |  2480 |
| ["2016-06-09 03:00:00","2016-06-09 06:00:00") |   362 |
+-----------------------------------------------+-------+
(17 rows)


WITH句の最初のブロックが使うテーブルの指定、2番目のブロックが集計の起点および間隔の設定で、3・4番目のブロックは変更不要。最後のSELECT文で、実際の集計関数や式を書きます(countの所)。

ストアド化して集計の起点・時間間隔を引数で受け取るようにすればもっと便利かも。そのうち試します。