Running an SQL query in rails
I have a rails app where I need to subtract 2 timestamps and display the result on a webpage.
I have used the following sql statement and I am getting results.
select (julianday(resolutions.created_at)-julianday(tickets.created_at))*24 from tickets,resolutions
tickets.created_at value is 2010-04-05 18:59:02 which is the timestamp and resolution.created_at value is 2010-04-08 08:10:33
Now where do I put this sql so that it can be seen on my webpage. I tried it on the views page with the following, but nothing came up:
<% @sql = "select (julianday(resolutions.created_at)-julianday(tickets.created_at))*24 from tickets,resolutions" %>
<% @t=(ActiveRecord::Base.connection.execute(@sql)) %>
So how do I display it on my web page?
when i do the above i get the output printed on the webpage like
061.1919444389641(julianday(resolutions.created_at)-julianday(tickets.created_at))*2461.1919444389641
only 61.1919444389641 should be printed, but also prompted.
source to share
In your controller
@tickets = Tickets.find(
:all,
:joins => :resolution,
:select => '(julianday(resolutions.created_at)-julianday(tickets.created_at))*24 AS interval'
)
In your opinion
<% @tickets.each do |ticket|%>
<%= ticket.interval %>
<% end %>
Typically, you would put this logic in one of your models, for example:
In your ticket model
def time_to_resolve
resolution.created_at - created_at
end
To reduce the number of requests when repeating multiple requests, you should use this:
Tickets.find(:all, :include => :resolution)
source to share