CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
denispolicarpocampos
denispolicarpocampos

Reputation: 69

Get only one record from each day

I have a Currency table and I want to capture all values ​​with the date value within 1 week. I did this query and managed to get the result:

Currency.where (date: (1.week.ago..Date.today.end_of_day)

This date field is a datetime.

I now want to get only the record with the highest date recorded on the day. That is, I want to get 7 records (one from each day of the week) and this record is the last recorded (highest value in the date column) of that particular day.

How can I make this query in the active record?

I tried using date_trunc but I did not succeed. My database is postgresql

DLL Of table:

Currency(id: integer, currency_kind: integer, value: float, date: datetime, created_at: datetime, updated_at: datetime)

class CreateCurrencies < ActiveRecord::Migration[5.2]
  def change
    create_table :currencies do |t|
      t.integer :currency_kind
      t.float :value
      t.datetime :date

      t.timestamps
    end
  end
end

Example with Currency.all

=> #<ActiveRecord::Relation [#<Currency id: 51, currency_kind: "eur", value: 4.18835, date: "2019-02-04 05:37:59", created_at: "2019-02-04 03:08:08", updated_at: "2019-02-04 03:08:08">, #<Currency id: 52, currency_kind: "usd", value: 3.6593, date: "2019-02-04 05:37:59", created_at: "2019-02-04 03:08:08", updated_at: "2019-02-04 03:08:08">, #<Currency id: 53, currency_kind: "aud", value: 2.65061, date: "2019-02-04 05:37:59", created_at: "2019-02-04 03:08:08", updated_at: "2019-02-04 03:08:08">, #<Currency id: 54, currency_kind: "eur", value: 4.18755, date: "2019-02-04 02:47:58", created_at: "2019-02-04 03:18:13", updated_at: "2019-02-04 03:18:13">, #<Currency id: 55, currency_kind: "usd", value: 3.6593, date: "2019-02-04 02:47:59", created_at: "2019-02-04 03:18:13", updated_at: "2019-02-04 03:18:13">, #<Currency id: 56, currency_kind: "aud", value: 2.6497, date: "2019-02-04 02:47:59", created_at: "2019-02-04 03:18:13", updated_at: "2019-02-04 03:18:13">, #<Currency id: 57, currency_kind: "eur", value: 4.19655, date: "2019-02-04 22:55:59", created_at: "2019-02-04 23:26:40", updated_at: "2019-02-04 23:26:40">, #<Currency id: 58, currency_kind: "usd", value: 3.6692, date: "2019-02-04 22:55:59", created_at: "2019-02-04 23:26:40", updated_at: "2019-02-04 23:26:40">, #<Currency id: 59, currency_kind: "aud", value: 2.6499, date: "2019-02-04 22:55:59", created_at: "2019-02-04 23:26:40", updated_at: "2019-02-04 23:26:40">, #<Currency id: 60, currency_kind: "eur", value: 4.1943, date: "2019-02-04 23:27:02", created_at: "2019-02-04 23:27:09", updated_at: "2019-02-04 23:27:09">, ...]>

Upvotes: 1

Views: 429

Answers (1)

NeverBe
NeverBe

Reputation: 5038

You need something like this

Currency.select("rate, MAX(DATE_FORMAT(date, '%Y-%m')) as max_date").where(date: 1.week.ago..Date.today.end_of_day).group("DATE_FORMAT(date, '%Y-%m')")

Upvotes: 0

Related Questions