[Rails] Agregate (sum) by date

I need to sum some data for each day, but SQL is very poor standardized
when converting time or date to string. For example these are two
queries that do same thing in sqlite and postgres (and mssql has way
different approach too)

pgsql = <<esql
select to_char(time_created,'YYYYMMDD') as mydate, sum(time_spent)
from docs
where to_char(time_created,'YYYYMMDD') >= '#{date_from}' and
to_char(time_created,'YYYYMMDD') <= '#{date_to}'
group by to_char(time_created,'YYYYMMDD')
order by mydate
esql

sqlite = <<esql
select strftime('%Y%d%m',time_created) as mydate, sum(time_spent)
from docs
where strftime('%Y%d%m',time_created) >= '#{date_from}' and
strftime('%Y%d%m',time_created) <= '#{date_to}'
group by strftime('%Y%d%m',time_created)
order by mydate
esql

Is there a better way of doing this.

by
TheR

--
Posted via http://www.ruby-forum.com/.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.