0

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;");

The result of my query is 

"id"   "latency"
 104     5928
 106     5917
 158     5751
 162     5736
 95      5940
 85      5950

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

summu
  • 388
  • 2
  • 7
  • 25
  • This might be helpful: https://stackoverflow.com/a/10607102/2640181 – barmic Nov 28 '18 at 06:26
  • You can do something like this `SELECT COUNT(table_name.id) FROM generate_series(timestamp '2000-01-01 00:00' , timestamp '2000-01-02 00:00' , interval '24 hours') t(x);` – Vishal Nov 28 '18 at 06:39
  • @Vishal This doesn't work, as i have run the above query which i put in the `result ` variable, to the result which you can can see in my updated question while using this query how would I write your generate_series part – summu Dec 01 '18 at 13:58

0 Answers0