PostgreSQL: Select All Records from Last 24 Hours

Often in PostgreSQL you may want to select all records in a table that have a timestamp within the last 24 hours.

You can use the following basic syntax to do so:

SELECT *
FROM emp
WHERE start_time > NOW() - interval '24 hour';

This particular example selects all records from the table named emp that have a timestamp from the last 24 hours in the start_time column.

Note that we use the NOW() command to return the current date and time in PostgreSQL.

We then subtract 24 hours from this time and filter the table to only show rows where the time in the start_time column is more recent than 24 hours ago.

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

Example: Select All Records from Last 24 Hours in PostgreSQL

Suppose that we create the following table named emp that contains information about when various employees at some company started working on a specific task:

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

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2024-08-28 04:15:00', 134);
INSERT INTO emp VALUES (0002, '2024-08-25 04:05:00', 201);
INSERT INTO emp VALUES (0003, '2024-08-28 07:00:00', 155);
INSERT INTO emp VALUES (0004, '2024-08-27 09:15:00', 98);
INSERT INTO emp VALUES (0005, '2024-08-27 18:56:00', 67);

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

 empid |     start_time      | sales 
-------+---------------------+-------
     1 | 2024-08-28 04:15:00 |   134
     2 | 2024-08-25 04:05:00 |   201
     3 | 2024-08-28 07:00:00 |   155
     4 | 2024-08-27 09:15:00 |    98
     5 | 2024-08-27 18:56:00 |    67

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

Suppose that we would like to select only the records where the time in the start_time column is within the last 24 hours.

We can use the following syntax to do so:

SELECT *
FROM emp
WHERE start_time > NOW() - interval '24 hour';

 empid |     start_time      | sales 
-------+---------------------+-------
     1 | 2024-08-28 04:15:00 |   134
     3 | 2024-08-28 07:00:00 |   155
     5 | 2024-08-27 18:56:00 |    67

The resulting table only contains the records where the time in the start_time column is within the last 24 hours.

Feel free to use similar syntax to filter your own table to only display records where a timestamp is within the last 24 hours.

Additional Resources

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

PostgreSQL: Calculate Timestamp Difference in Hours
PostgreSQL: Calculate Timestamp Difference in Minutes
PostgreSQL: Calculate Difference Between Dates in Days