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.

Search Discussions

  • Gundestrup at Sep 26, 2011 at 7:59 am
    Look at the by_star gem.
    https://github.com/radar/by_star

    regards
    svend
    On Sep 26, 3:29 am, Damjan Rems wrote:
    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 viahttp://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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedSep 26, '11 at 7:29a
activeSep 26, '11 at 7:59a
posts2
users2
websiterubyonrails.org
irc#RubyOnRails

2 users in discussion

Damjan Rems: 1 post Gundestrup: 1 post

People

Translate

site design / logo © 2021 Grokbase