An ASOF join fills gaps between unequal timestamps by selecting a value valid at or before each event. It suits prices, stock quotes, and sensor readings when the two relations use different time intervals. section 4.8
Attach the latest valid price to every sale
Generate prices every 15 minutes and sales every 5 minutes, then join with: ASOF JOIN prices ON prices.valid_at <= sales.sold_at. DuckDB returns all 12 sales, assigning each sale the closest price at or before its timestamp. An equality join returns only the four exact timestamp matches. section 4.8
The inequality controls validity: <= uses values already effective at the event time, while ASOF matching carries each price forward until the next one becomes valid. The result can feed other SQL constructs, including a window running total. section 4.8
Accumulate photovoltaic earnings
Join daily power production to prices with prices.valid_from <= power.day, multiply price by kWh, and apply SUM(...) OVER (ORDER BY power.day) to calculate accumulated earnings. section 4.8
Listing 4.27 Using an inner join for timestamps
WITH prices AS (
SELECT range AS valid_at,
random()*10 AS price
FROM range(
'2023-01-01 01:00:00'::timestamp,
'2023-01-01 02:00:00'::timestamp, INTERVAL '15 minutes')
),
sales AS (
SELECT range AS sold_at,
random()*10 AS num
FROM range(
'2023-01-01 01:00:00'::timestamp,
'2023-01-01 02:00:00'::timestamp, INTERVAL '5 minutes')
)
SELECT sold_at, valid_at AS 'with_price_at', round(num * price,2) as price
FROM sales
JOIN prices ON prices.valid_at = sales.sold_at;
Compare the equality-join baseline with the ASOF version to see why exact timestamp matching loses events. section 4.8
