0

Here's my problem:

This particular table stores information about visits that clients have made so far

'Income' controller (basic summing) - by days/months/years

def index
 @days = Visit.select("start_time,SUM(price) as price").group("strftime('%Y-%m-%d', start_time)").where(:start_time => DateTime.now.beginning_of_month..DateTime.now.end_of_month).where(:status => true).order('start_time DESC').all
 @months = Visit.select("start_time,SUM(price) as price").group("strftime('%Y-%m', start_time)").where(:status => true).order('start_time DESC').all
 @years = Visit.select("start_time,SUM(price) as price").group("strftime('%Y', start_time)").where(:status => true).order('start_time DESC').all
 @total = 0
 @years.collect { |x| @total = @total + x.price }
end

Switching to Postgres from sqlite made strftime impossible to use - I tried changing it to something like this:

 @days = Visit.select("to_char(start_time, 'YYYY-MM-DD'),SUM(price) as price").group("to_char(start_time,'YYYY-MM-DD')").where(:start_time => DateTime.now.beginning_of_month..DateTime.now.end_of_month).where(:status => true).order("to_char(start_time,'YYYY-MM-DD') DESC").all
 @months = Visit.select("to_char(start_time, 'YYYY-MM'),SUM(price) as price").group("to_char(start_time,'YYYY-MM')").where(:status => true).order("to_char(start_time,'YYYY-MM') DESC").all
 @years = Visit.select("to_char(start_time, 'YYYY'),SUM(price) as price").group("to_char(start_time,'YYYY')").where(:status => true).order("to_char(start_time,'YYYY') DESC").all

But now I get error

missing start_time

in View:

 <table class="table table-hover table-striped table-bordered">
  <thead>
  <tr>
    <th class="text-center">Time period</th>
    <th class="text-center">Sum</th>
  </tr>
  </thead>
  <tbody>
  <tr>
    <th colspan="2">days</th>
  </tr>
  <% @days.each do |s| %>
      <tr>
        <td><%= s.start_time.strftime('%d.%m.%Y') %></td>
        <td class="text-right"><%= s.price %> eur</td>
      </tr>
  <% end %>
  <tr>
    <th colspan="2">months</th>
  </tr>
  <% @months.each do |s| %>
      <tr>
        <td><%= s.start_time.strftime('%m.%Y') %></td>
        <td class="text-right"><%= s.price %> eur</td>
      </tr>
  <% end %>
  <tr>
    <th colspan="2">years</th>
  </tr>
  <% @years.each do |s| %>
      <tr>
        <td><%= s.start_time.strftime('%Y') %>r</td>
        <td class="text-right"><%= s.price %> eur</td>
      </tr>
  <% end %>
  <tr>
    <th class="text-right">All</th>
    <td class="text-right"><%= @total %> eur</td>
  </tr>
  </tbody>
</table>

I don't really understand why do I get such an error.

  • You don't need to convert the `start_time` in the query. If `start_time` is a datetime type in the db, it will be able to compare the stored `start_time` to `Date.today` – gwalshington Jan 19 '19 at 23:31
  • Thank you, that worked! But now I can't get grouping by months and years to work. –  Jan 23 '19 at 20:26
  • Check this out: https://stackoverflow.com/questions/902974/grouping-by-week-month-etc-activerecord – gwalshington Jan 23 '19 at 20:27

2 Answers2

0

For conversion of date (instead of datetime) you can use to_date or to_timestamp https://www.postgresql.org/docs/9.6/functions-formatting.html

Michal
  • 46
  • 2
  • Or you can use more explicit arguments with `to_char`: `to_char(now(),'YYYY-mm-dd HH24:MM:ss')` – Michal Jan 19 '19 at 23:24
0

You can also use DateTime#iso8601 to convert the date to a string

https://ruby-doc.org/stdlib-2.3.3/libdoc/date/rdoc/DateTime.html#method-i-iso8601

Diego Camargo
  • 1
  • 1