-1

I am using postgresql. I have one table the Shipments table and I have two date columns on the table called arrival_date and release_date, what I wanted to do is to get all the record and sort it according to the lowest days difference of arrival_date and release_date, for example:

Arrival Date, Released Date
2017-06-04,   2017-06-30
2017-05-02,   2017-05-05

So in this example the days difference of first record is 26 days and the second one is 3 days, so the second record should be the first one.

Ryan Angelo
  • 93
  • 1
  • 13

3 Answers3

0

I think that you need improve your query in postgres

in example

Table
id   |  dateA   |   dateB

select dateA, dateB, age(timestamp dateA, timestamp dateB) diffdate from Table order by diffdate desc;
Emiliano
  • 698
  • 9
  • 30
0

You may want to use sort_by! method:

Shipment.all.sort_by!{ |shipment| shipment.released_date - shipment.arrival_date }
Othmane El Kesri
  • 603
  • 5
  • 16
0

The easiest way would be to subtract the two dates using Postgres:

ordered_shipments = Shipment.order("(arrival_date - released_date) ASC")

This will subtract the Unix timestamps of the two dates and sort the difference in ascending order.

I got inspiration from this answer.

Alexander
  • 3,959
  • 2
  • 31
  • 58
  • thanks for this answer! how about if there are two records with the same days difference, for example 2 records have the same days difference lets say 2 days difference, I want to get those two records with the same days diffrence – Ryan Angelo Jun 28 '17 at 12:24