PostgreSQL: How to Select Row with Closest Date

Often in PostgreSQL you may want to select a row from a table with the closest date to a specified date.

You can use the following syntax to do so:

SELECT *
FROM products
ORDER BY abs(date_sold - date '2024-09-19') 
LIMIT 1;

This particular example selects the row from the products table with a date in the date_sold column that is closest to 2024-09-19.

Here is how this syntax works:

• First, we use SELECT * to select all rows from the products table.
• Then, we use the ORDER BY clause to order the rows based on the absolute difference between the date in the date_sold column and 2024-09-19.
• Lastly, we use LIMIT 1 to only return the first row, which is the row with the smallest absolute difference between the dates.

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

Example: How to Select Row with Closest Date in PostgreSQL

Suppose that we create the following table named products that contains information about when various products were sold by some company:

-- create table named 'products'
CREATE TABLE products (
  productID INTEGER PRIMARY KEY,
  name TEXT,
  date_sold DATE
);

-- add rows to the table 
INSERT INTO products VALUES (0001, 'A', '2024-09-04');
INSERT INTO products VALUES (0002, 'A', '2024-09-12');
INSERT INTO products VALUES (0003, 'B', '2024-09-10');
INSERT INTO products VALUES (0004, 'B', '2024-09-15');
INSERT INTO products VALUES (0005, 'C', '2024-09-18');
INSERT INTO products VALUES (0006, 'C', '2024-09-27');

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

 productid | name | date_sold  
-----------+------+------------
         1 | A    | 2024-09-04
         2 | A    | 2024-09-12
         3 | B    | 2024-09-10
         4 | B    | 2024-09-15
         5 | C    | 2024-09-18
         6 | C    | 2024-09-27

Suppose that we would like to select the row with the date in the date_sold column that is closest to 2024-09-19.

We can use the following syntax to do so:

SELECT *
FROM products
ORDER BY abs(date_sold - date '2024-09-19') 
LIMIT 1;

 productid | name | date_sold  
-----------+------+------------
         5 | C    | 2024-09-18

Notice that this returns the row with the date 2024-09-18 in the date_sold column, which represents the row with the closest date to 2024-09-19.

You can use similar syntax to select the row with a date closest to a specified date in a column of your own table.

Note: If you would like to return the N closest rows then change LIMIT 1 to a different number. For example, you could use LIMIT 3 to return the rows with the 3 closest date values in a date column.

Additional Resources

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

PostgreSQL: How to Select All Records from Yesterday
PostgreSQL: Select All Records from Last 7 Days
PostgreSQL: Calculate Difference Between Dates in Days