PostgreSQL: Select All Records from Last 7 Days

Often in PostgreSQL you may want to select all records from a table with a date in the last 7 days.

You can use the following syntax to do so:

SELECT *
FROM emp
WHERE start_date > CURRENT_DATE - interval '7 days';

This particular example will select all records from the table named emp where the date in the start_date column is within the last 7 days (including the current date). 

Note that the CURRENT_DATE command in PostgreSQL returns the current date.

Using this query, we are able to subtract 7 days from the current date and filter the table to only show records where the date in the start_date column is “greater” than 7 days ago.

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

Example: Select All Records from Last 7 Days in PostgreSQL

Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT PRIMARY KEY,
  start_date DATE,
  sales INT
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2024-08-01', 134);
INSERT INTO emp VALUES (0002, '2024-07-01', 201);
INSERT INTO emp VALUES (0003, '2024-08-22', 155);
INSERT INTO emp VALUES (0004, '2024-08-26', 98);
INSERT INTO emp VALUES (0005, '2024-08-28', 65);
INSERT INTO emp VALUES (0006, '2024-06-20', 52);
INSERT INTO emp VALUES (0007, '2024-08-22', 50);

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

 empid | start_date | sales 
-------+------------+-------
     1 | 2024-08-01 |   134
     2 | 2024-07-01 |   201
     3 | 2024-08-22 |   155
     4 | 2024-08-26 |    98
     5 | 2024-08-28 |    65
     6 | 2024-06-20 |    52
     7 | 2024-08-22 |    50

This article is currently being written on 8/28/2024.

Suppose that we would like to select all records from this table where the date in the start_date column is from the last 7 days.

We can use the following syntax to do so:

SELECT *
FROM emp
WHERE start_date > CURRENT_DATE - interval '7 days';

 empid | start_date | sales 
-------+------------+-------
     3 | 2024-08-22 |   155
     4 | 2024-08-26 |    98
     5 | 2024-08-28 |    65
     7 | 2024-08-22 |    50

The resulting table contains all records from the original table where the date in the start_date column is from the last 7 days.

Feel free to use similar syntax to select all records from the last 7 days in your own table.

Additional Resources

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

PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Extract Date from a Timestamp
PostgreSQL: How to Extract Month from Date
PostgreSQL: How to Extract Year from Date