pgsql>5分おきの時間に変換し集計したい

Question

データを時間で集計しようとしています。

データベース:Postgresql 9.0.23

motoテーブル
番号 sirial
出来日 date
出来時刻 timestamp

出来時刻のまま表にすると出来日によって比べにくい為、
5分おきにまとめて集計をしたいと思っています。

with toki as (
select 番号,mod(cast(date_part('minute',出来時刻)::double precision as integer),5) as bt from moto where 出来日=CURRENT_DATE
)
select case toki.bt when 0 then to_char(moto.出来時刻,'HH24:MI')
else moto.出来時刻 + (cast('5 minutes' as interval)-cast('toki.bt minutes' as interval))
end as jikan ,
count(*)
from moto,toki
where moto.番号=toki.番号 and moto.出来日=CURRENT_DATE
group by jikan
order by jikan;

1-5分は 「5分」、6-10分は「10分」にまとめたいと思っています。

with部分で「分」の下1桁を取り出して、5分で割った余りを計算させています。
selectにあるcase部分で、余りがゼロならその値を「時分」表記に。
ゼロ以外なら、あまりから差引して「5」か「10」になるように計算させています。

ですが、どうしてもcast elseの部分にある計算ができません。
ime without time zone と integerの違いで、修正するたびに引っかかり計算が
成り立ちません。

こういった時の計算方法はどうすればよいか、アドバイスをお願いいたします。

Answer 1

timestamp型の分を切り捨てしたものと、5分単位で切り捨てた分を足し合わせすれば求めるものが算出できます。
DATE_TRUNC()、EXTRACT()、TRUNC()などを用います。

select  *
      , DATE_TRUNC('hour', 出来時刻) 
        + (TRUNC(EXTRACT('minute' FROM 出来時刻) / 5 ) * 5 || 'minutes') ::interval
        as jikan
from moto
where 出来日=CURRENT_DATE

※上記は5分刻みですが、以下の様な不規則な刻みにはなっていませんので必要なら調整して下さい。

1-5分は 「5分」、6-10分は「10分」にまとめたいと思っています。

Comments

[YosiyukiUsijima]

コメントありがとうございました。
教えて頂いたSQLは5分前にまとめるものでしたので、
プラス5を追記したら思っていたようになりました。
一週間以上停滞していたのですが、これでようやく前に進みます。
ありがとうございました。

(trunc(extract('minute' from 修理完了時刻)/5)*5+5|| 'minutes')::interval

[sazi]

別な回答にあるコメントの
DBの値 -> 変換後の値
13:00:00 -> 13:00
13:01:00 -> 13:05
については、+5すると、その通りにはなりませんし、55分以上は60分になり時刻がズレますけど、良いのでしょうか？
ずらすなら、元の値である修理完了時刻に対して演算すべきです、
表示の文言を加工したいなら、計算はそのままで、計算値を元に別に加工した方が良いように思います。

Answer 2

9.9. 日付/時刻関数と演算子
EXTRACT(epoch FROM toki.bt)
みたいにすれば、エポック秒による日時の計算ができるようになるため、
基準の日時のエポック秒、そこからの5分ごとの数字を求めるといいかもね。

Comments

[YosiyukiUsijima]

コメントをありがとうございました。
時間が数値になったので、5分になる計算をしていたのですが、
あれこれやってみたのですが、どうしても思ったようになりませんでした。
説明が悪かったように思います。申し訳ありませんでした。
以下のように時間を変換して、集計をしたかったのです。

DBの値 -> 変換後の値
13:00:00 -> 13:00
13:01:00 -> 13:05
13:02:00 -> 13:05
13:03:00 -> 13:05
13:04:00 -> 13:05
13:05:00 -> 13:05
13:06:00 -> 13:10
13:07:00 -> 13:10
13:08:00 -> 13:10
13:09:00 -> 13:10
13:10:00 -> 13:10

最終的にはこの値をhtmlのcanvusでグラフを書く予定です。
実際の時間はもっと細かいので、棒グラフが細かくなりすぎる為、
横軸5分おきに区切りたいと思い作成しています。