CODEWITHSUNDEEP
mysqlsqlruby-on-railsruby-on-rails-4
AlexLarra
AlexLarra

Reputation: 881

In Rails how to get the average of the substract of two datetime columns

I need to get the average of updated_at - created_at datetime columns

I tried this way but it does not work as I expected:

User.average('updated_at - created_at')

It is giving this sql query:

SELECT AVG(updated_at-created_at) FROM `users`

When I filter by one ID getting one object it is revealing me different result than user.updated_at - user.created_at. That is why I think my sql query is not correct.

User.where(id: 3).average('updated_at - created_at').to_f => 29183.0

user = User.find(3) # (created_at: "2018-07-25 09:58:56", updated_at: "2018-07-25 12:50:39")
user.updated_at - user.created_at => 10303.0

Upvotes: 2

Views: 545

Answers (1)

AlexLarra
AlexLarra

Reputation: 881

Finally I found my own answer using TIME_TO_SEC and TIMEDIFF inside AVG:

User.where(id: 3)
    .average('TIME_TO_SEC(TIMEDIFF(updated_at, created_at))')
    .to_f
 => 10303.0

Upvotes: 3

Related Questions