Sumif in Postgresql between two tables

Question

These are my two sample tables.

table "outage" (column formats are text, timestamp, timestamp)

+-------------------+----------------+----------------+
| outage_request_id |  actual_start  |   actual_end   |
+-------------------+----------------+----------------+
| 1-07697685        | 4/8/2015 4:48  | 4/8/2015 9:02  |
| 1-07223444        | 7/17/2015 4:24 | 8/01/2015 9:23 |
| 1-07223450        | 2/13/2015 4:24 | 4/29/2015 1:03 |
| 1-07223669        | 4/28/2017 9:20 | 4/30/2017 6:58 |
| 1-08985319        | 8/24/2015 3:18 | 8/24/2015 8:27 |
+-------------------+----------------+----------------+

and a second table "prices" (column format is numeric, timestamp)

+-------+---------------+
| price |     stamp     |
+-------+---------------+
| -2.31 | 2/1/2018 3:00 |
| -2.35 | 2/1/2018 4:00 |
| -1.77 | 2/1/2018 5:00 |
| -2.96 | 2/1/2018 6:00 |
| -5.14 | 2/1/2018 7:00 |
+-------+---------------+

My Goal: To sum the prices in between the start and stop times of each outage_request_id.

I have no idea how to go about properly joining the tables and getting a sum of prices in those outage timestamp ranges.

Hambone · Accepted Answer · 2018-04-06 00:29:01Z

2

I can't promise this is efficient (in fact for very large tables I feel pretty confident it's not), but this should notionally get you what you want:

select
  o.outage_request_id, o.actual_start, o.actual_end,
  sum (p.price) as total_price
from
  outage o
  left join prices p on
    p.stamp between o.actual_start and o.actual_end
 group by
   o.outage_request_id, o.actual_start, o.actual_end

answered Apr 6, 2018 at 0:29

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

4 Comments

otterdog2000 Over a year ago

hmm. I'm getting a null price sum. One wrinkle is that the outage table is timestamp WITH timezone while the prices table is timestamp WITHOUT timezone. Should that matter for the join? The times are actually correct. So my timestamp in table "outage" looks like 2015-04-08 04:48:00-04. And my timestamp in "prices" looks like 2018-01-01 01:00:00

otterdog2000 Over a year ago

the format difference being the "-04" on the end of the outage table timestamps.

Hambone Over a year ago

it looks like the outage table has timestamps with time zones and the prices table has it without time zones. I'm actually not sure how the equality relationships work in this case, but I'm PostgreSQL has a pretty good track record of getting it right in cases like this. Do you know why the different datatypes, and can you harmonize them?

otterdog2000 Over a year ago

Ah ha! My actual code incorrectly had a timestamp WHERE clause in the price table that didn't span any of the outage times. Just had to remove that. Your code works great. Thanks.

Collectives™ on Stack Overflow

Sumif in Postgresql between two tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related