Using temporal joins with ASOF

Joining tables usually involves linking a common attribute across a table, such as finding the same identifier, name, or code value. DuckDB can also support “fuzzy” joins when we wish to join on values that may be close (but not identical) across tables. This is especially true when it comes to “temporal joins” – or joins that match on moments in time.

In this section, we will be discussing temporal joins and introducing the ASOF join, which can simplify joins across time.

For this exercise, we will be looking at historic weather measurements to see if bad weather is affecting our skier’s performance. Let’s start by loading data into the weather table:

CREATE OR REPLACE TABLE weather AS
SELECT *
FROM read_csv('weather.csv', timestampformat='%Y-%m-%d %H:%M:%S');

Let’s take a peek at the data within our newly created weather table:

SELECT *
FROM weather
LIMIT 10;

...