mardi 27 novembre 2018

query result in set of interval ranges in postgresql(rails)

I have a timestamp column for which i have to calculate the time difference and divide it into certain set of intervals

for time difference in hours i have written this query

 result = ActiveRecord::Base.connection.exec_query("SELECT id,(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - image_retouch_items.created_at)/3600)::INTEGER AS latency FROM image_retouch_items WHERE status= 0;");

How to get result as set of intervals(hours),like for row for which time difference lie between the range of 0-24 hr increment the count . i.e.

 interval    count
  0-24         2
 24-48         3
 48-72         0  

How to get that in single query

Aucun commentaire:

Enregistrer un commentaire