如何按特定间隔获取两时间戳间数据并填充NULL值（PostgreSQL）

Hey there! Let's fix that gap in your price data where dates like Dec 15 and 16, 2006 are missing. The core idea here is to first generate a continuous sequence of dates covering your desired range, then left-join it with your existing daily price data to fill in those missing spots with NULL values.

Step-by-Step Solution #

Here's a complete, tested query that does exactly what you need:

WITH daily_prices AS (
  -- Grab the first price entry for each day (your original logic, refined)
  SELECT DISTINCT ON (date_trunc('day', p.date))
    p.code,
    p.price, -- Replace with your actual price column name if different
    date_trunc('day', p.date) AS day_date
  FROM price_events p
  WHERE p.code = 'BCI.AX'
  ORDER BY date_trunc('day', p.date), p.date -- Critical for stable DISTINCT ON results
),
date_range AS (
  -- Generate every day between your earliest and latest price dates
  SELECT date_trunc('day', gs.dt) AS day_date
  FROM generate_series(
    (SELECT MIN(day_date) FROM daily_prices),
    (SELECT MAX(day_date) FROM daily_prices),
    INTERVAL '1 day'
  ) gs(dt)
)
-- Combine the date range with your prices, filling gaps with NULL
SELECT 
  'BCI.AX' AS code,
  dp.price,
  dr.day_date
FROM date_range dr
LEFT JOIN daily_prices dp ON dr.day_date = dp.day_date
ORDER BY dr.day_date;

How It Works #

• daily_prices CTE: This replicates your original logic but explicitly extracts the truncated day for easier joining. The ORDER BY clause is mandatory with DISTINCT ON to ensure we consistently pick the first entry of each day.
• date_range CTE: Uses PostgreSQL's built-in generate_series function to create a list of every single day between the earliest and latest dates in your price data. No more missing days!
• Final Left Join: By joining the continuous date range with your daily prices, every date will appear in the result. For days with no price data (like Dec 15 and 16), the price column will show NULL.

Customize the Date Range #

If you want to use a fixed date range instead of relying on the min/max dates from your table, just update the generate_series parameters:

generate_series(
  '2006-12-10'::TIMESTAMP, -- Your custom start date
  '2006-12-22'::TIMESTAMP, -- Your custom end date
  INTERVAL '1 day'
)

When you run this query, your result will include all days in the specified range, with NULLs filling in the gaps where no price events exist.

内容的提问来源于stack exchange，提问作者Dominic Bou-Samra