CODEWITHSUNDEEP
sqlpostgresqltime-series
Luke
Luke

Reputation: 375

Map values from one table to another table based on timestamp in postgresQL

I have two tables. A transaction table that stores prices at specific dates (with precise timestamps). And a price-history table where we have one row for every day. I now want to put the price from the transaction table in the price-history table where the timestamps match (same day). The below photo should help to clarify what I need. The price column in the price-history table is the needed result. enter image description here

Upvotes: 2

Views: 1342

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

To update the table, you would use:

update price_history ph
    set price = t.price
    from transactions t
    where t.date::date = ph.date;

Note: If there are two transactions on the same date, an arbitrary one is used for the update.

If you need to construct the price history table, you might construct it first with 0 values and then update:

create table price_history (
    date date primary key,
    price numeric(10, 2)  -- or whatever
);

insert into price_history (date, price)
    select gs.dte, 0
    from generate_series('2021-01-01'::date, '2021-12-31'::date, interval '1 day') gs(dte);

And then update the values as above.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

A simple join should work here:

SELECT t.date, ph.price
FROM Transactions t
INNER JOIN "price-history" ph
    ON ph.date = t.date::date

This assumes that the price-history table is like a calendar table, and has data for every date of interest. If not, then we should modify the above to use a left join, and also we should select COALESCE(ph.price, 0) instead of just ph.price.

Upvotes: 2

Related Questions