Writing SQL query with a date range using postgresql and ruby on rails

Question

I am having problem writing a sql statement with the following tables. I have two tables one is meter another one is meter_info. meter table has below columns

id, name, created_at

and meter_info has bellow columns

id, voltage, meter_id, created_at

data is being saved continuously to meter_info table. I want to write a query that will take a date range and give me exactly one meter info within each date for each of the meter.

so let's say I have three meters in my meter table.

 id     name      created_at
 1      meter-a   2017-10-10
 2      meter-b   2017-10-11

and in my meter table i have alot of data

id    voltage    created_at     meter_id
1      15         2017-10-10      1
2      16         2017-10-10      1
3      14         2017-10-10      2
4      15         2017-10-10      2
5      13         2017-10-11      1
6      11         2017-10-11      1
7      13         2017-10-11      2
8      12         2017-10-11      2

Now I want to write a query that will take a date range parameter and out put like bellow(data range is 2017-10-10to 2017-10-11)

created_at     meter_id     voltage
2017-10-10         1           16
2017-10-10         2           15
2017-10-11         1           11
2017-10-11         2           12

so I want the last record of each meter within the date. I don't know how to write the sql query. I am using Postgresql and Ruby on Rails. Thanks a ton in advance.

Dmitry Umarov · Accepted Answer · 2017-10-11 14:28:03Z

1

You must use DISTINCT ON clause.

SELECT DISTINCT ON (created_at, meter_id) created_at, meter_id, voltage
FROM meter_info
WHERE created_at BETWEEN '2017-10-10' AND '2017-10-11'
ORDER BY created_at, meter_id, id DESC
;

answered Oct 11, 2017 at 14:28

Dmitry Umarov

4763 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

João Costa · Accepted Answer · 2017-10-11 15:06:32Z

1

If you want to use rails active record you can do it this way:

start_date = Date.new 2017, 10, 10
end_date = Date.new 2017, 10, 11
MeterInfo.select("DISTINCT ON (created_at::date, meter_id) * ").where(created_at: start_date..end_date).order("created_at::date, meter_id, id DESC")

Note that I'm assuming your 'created_at' column is a datetime and thus need to be converted to a date for grouping using postgresql ::date.

answered Oct 11, 2017 at 15:06

João Costa

3544 silver badges12 bronze badges

Collectives™ on Stack Overflow

Writing SQL query with a date range using postgresql and ruby on rails

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related