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

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

Is there a better way of doing this.


Look at the by_star gem.


This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs