CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsaverage
chendriksen
chendriksen

Reputation: 1024

Average time between dates in two tables

In our database, we've got a coffee_types table, and a shipments table.

Each coffee in coffee_types has a roasted_at date, each shipment has a shipped_at date.

I'd like to find the average between roasted_at and shipped_at for each coffee type, so this means making an average of all the shipped_at - roasted_at dates for each coffee.

How can I do this? The best I've managed is this, but it just gets the date difference per shipment:

SELECT coffee_types.name
     , date_part('day', age(coffee_types.roasted_at, shipments.shipped_at))
FROM coffee_types, shipments
WHERE coffee_types.id = shipments.coffee_type_id
GROUP BY coffee_types.name, coffee_types.roasted_at, shipments.shipped_at;

Upvotes: 0

Views: 242

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

To get the "age" of each coffee shipment, just subtract the timestamps producing an interval - or if you actually have date columns (your secret), the subtraction produces a simple integer:

SELECT c.name, s.shipped_at - c.roasted_at AS time_passed
FROM   coffee_types c
JOIN   shipments    s ON s.coffee_type_id = c.id
GROUP  BY c.name;

You don't need the age() function for that. Format the interval any way you like.

To get the average time passed between roasting and shipping, group by name only - or, to be precise, any UNIQUE (combination of) column(s) of the coffee_types table).
Do not group by columns you want to compute an average from - that was your basic mistake:

SELECT c.name, avg(s.shipped_at - c.roasted_at) AS avg_time_passed
FROM   coffee_types c
JOIN   shipments    s ON s.coffee_type_id = c.id
GROUP  BY c.name;

Upvotes: 1

Related Questions