Often in PostgreSQL you may want to select records from a table where a timestamp column is between two specific timestamps.
You can use the following basic syntax to do so:
SELECT *
FROM emp
WHERE start_time BETWEEN timestamp '2024-09-20 04:00:00'
AND '2024-09-20 10:00:00';
This particular example selects all rows from the table named emp where the timestamp in the start_time column is between the following two timestamps:
- 2024-09-20 04:00:00
- 2024-09-20 10:00:00
The following example shows how to use this syntax in practice.
Example: How to Select Records Between Two Timestamps 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
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2024-09-20 11:15:23');
INSERT INTO emp VALUES (0002, '2024-09-20 04:05:56');
INSERT INTO emp VALUES (0003, '2024-09-20 07:00:10');
INSERT INTO emp VALUES (0004, '2024-09-20 09:15:00');
INSERT INTO emp VALUES (0005, '2024-09-20 12:56:30');
-- view all rows in table
SELECT * FROM emp;
empid | start_time
-------+---------------------
1 | 2024-09-20 11:15:23
2 | 2024-09-20 04:05:56
3 | 2024-09-20 07:00:10
4 | 2024-09-20 09:15:00
5 | 2024-09-20 12:56:30
Suppose that we would like to select only the rows where the timestamp in the start_time column is between the following times:
- 2024-09-20 04:00:00
- 2024-09-20 10:00:00
We can use the following syntax to do so:
SELECT *
FROM emp
WHERE start_time BETWEEN timestamp '2024-09-20 04:00:00'
AND '2024-09-20 10:00:00';
empid | start_time
-------+---------------------
2 | 2024-09-20 04:05:56
3 | 2024-09-20 07:00:10
4 | 2024-09-20 09:15:00
Notice that this returns only the rows where the timestamp in the start_time column is between the two timestamps that we specified.
Feel free to use similar syntax to select rows where a timestamp is between two specific times in your own table.
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
