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