Often in PostgreSQL you may want to select rows from a table where a timestamp column is empty.
You can use the following basic syntax to do so:
SELECT *
FROM emp
WHERE start_time IS NULL;
This particular example selects all rows from the table named emp where the timestamp in the start_time column is empty.
The following example shows how to use this syntax in practice.
Example: How to Select Rows where Timestamp is Empty 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
);
-- 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, NULL);
INSERT INTO emp VALUES (0004, '2024-09-20 09:15:00');
INSERT INTO emp VALUES (0005, NULL);
INSERT INTO emp VALUES (0006, '2024-09-20 12:56:30');
INSERT INTO emp VALUES (0007, '2024-09-20 12:58:32');
-- 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 |
4 | 2024-09-20 09:15:00
5 |
6 | 2024-09-20 12:56:30
7 | 2024-09-20 12:58:32
Suppose that we would like to only select the rows where the timestamp in the start_time column is empty.
We can use the following syntax to do so:
SELECT *
FROM emp
WHERE start_time IS NULL;
empid | start_time
-------+------------
3 |
5 |
Notice that this returns only the rows from the table where the timestamp in the start_time column is empty.
Feel free to use similar syntax to select rows where a timestamp column is empty 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
