calculate time difference in rails postgresql query

Question

I need to get time difference as query result in my rails(which has Postgres database)

Brute Force Approach

I queried all items from my database and then iterating each record and then calculating time difference in hours which is very slow.

1) @image_retouch_items = ImageRetouchItem.where(:status => '0') = Retrieved all data
2) @image_retouch_items.each do |retouch_item|
      latency_date = ((Time.parse(DateTime.now.to_s) - Time.parse(retouch_item.created_at.to_s))/3600).round
   end

Optimized

I need to calculate the difference of time(hours) in query itself, how to achieve that

like - ImageRetouchItem.where(:status => '0').select('(Time.parse(DateTime.now.to_s) - Time.parse(retouch_item.created_at.to_s))/3600).round')

moveson · Accepted Answer · 2018-11-21 15:03:07Z

4

Postgres can do this for you very easily using its internal current_timestamp:

ImageRetouchItem.where(:status => '0')
                .select("*, round(extract(epoch from(current_timestamp - created_at)) / 3600)::int as latency_date")

current_timestamp - created_at will return an interval. By extracting epoch from that interval, we convert it to a number of seconds, which we then divide by 3600 to get hours and round using the Postgres round() function. I went ahead and casted the result as an integer using ::int, but this is optional.

The image_retouch_item objects will now have a latency_date attribute that will contain the latency in hours, rounded to the nearest hour.

edited Nov 21, 2018 at 15:03

answered Nov 21, 2018 at 14:48

moveson

5,2231 gold badge17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

calculate time difference in rails postgresql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related