PostgreSQL: How to Fill Missing Dates

Often in PostgreSQL you may want to fill in missing dates in a date column.

You can use the generate_series function with the following syntax to do so:

SELECT x.sales_date, t.amount
   FROM (
      SELECT generate_series(min(sales_date), max(sales_date), '1d')::date AS sales_date
      FROM sales
       ) x
   LEFT JOIN sales t USING (sales_date)
   ORDER BY x.sales_date;

This particular example will fill in missing dates in the sales_date column of the sales table.

This syntax works by first using the generate_series() function to generate a series of dates that range from the minimum date to the maximum date in the sales_date column, then by left joining this range of dates to the existing dates in the sales table.

The following example shows how to use this syntax in practice.

Example: How to Fill Missing Dates in PostgreSQL

Suppose that we create the following table named sales that contains information about total sales made on various dates at some retail store:

-- create table named 'emp'
CREATE TABLE sales (
  sales_date DATE,
  amount INT
);

-- add rows to the table 
INSERT INTO sales VALUES ('2024-01-01', 134);
INSERT INTO sales VALUES ('2024-01-02', 201);
INSERT INTO sales VALUES ('2024-01-04', 155);
INSERT INTO sales VALUES ('2024-01-05', 109);
INSERT INTO sales VALUES ('2024-01-07', 120);
INSERT INTO sales VALUES ('2024-01-08', 116);

-- view all rows in table
SELECT * FROM sales;

 sales_date | amount 
------------+--------
 2024-01-01 |    134
 2024-01-02 |    201
 2024-01-04 |    155
 2024-01-05 |    109
 2024-01-07 |    120
 2024-01-08 |    116

Notice that there are two missing dates in the sales_date column between the starting and ending dates:

• 2024-01-03
• 2024-01-06

We can use the following syntax to fill in these missing dates:

SELECT x.sales_date, t.amount
   FROM (
      SELECT generate_series(min(sales_date), max(sales_date), '1d')::date AS sales_date
      FROM sales
       ) x
   LEFT JOIN sales t USING (sales_date)
   ORDER BY x.sales_date;

 sales_date | amount 
------------+--------
 2024-01-01 |    134
 2024-01-02 |    201
 2024-01-03 |       
 2024-01-04 |    155
 2024-01-05 |    109
 2024-01-06 |       
 2024-01-07 |    120
 2024-01-08 |    116

Notice that the missing dates have now been filled in the sales_date column.

The corresponding values in the amount column for these filled in dates are obviously empty.

You can use similar syntax to fill in missing dates in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Date from a Timestamp
PostgreSQL: How to Subtract Days from a Date
PostgreSQL: How to Select Rows in Date Range