Often in PostgreSQL you may want to extract the date from a timestamp.
You can use the following basic syntax to do so:
SELECT date(start_time) AS date_only
FROM emp;
This particular example extracts the date from the timestamp in the start_time column of the table named emp and saves the results to a new column named date_only.
Note that we use the date command to extract only the date from the timestamp in the start_time column.
The following example shows how to use this syntax in practice.
Example: How to Extract Date from a Timestamp 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-09-23 04:15:00', 134);
INSERT INTO emp VALUES (0002, '2024-04-01 04:05:00', 201);
INSERT INTO emp VALUES (0003, '2024-10-26 07:00:00', 155);
INSERT INTO emp VALUES (0004, '2024-12-14 09:15:00', 98);
INSERT INTO emp VALUES (0005, '2024-12-07 12:56:00', 67);
-- view all rows in table
SELECT * FROM emp;
empid | start_time | sales
-------+---------------------+-------
1 | 2024-09-23 04:15:00 | 134
2 | 2024-04-01 04:05:00 | 201
3 | 2024-10-26 07:00:00 | 155
4 | 2024-12-14 09:15:00 | 98
5 | 2024-12-07 12:56:00 | 67
Suppose that we would like to extract only the date from each timestamp in the start_time column.
We can use the following syntax to do so:
SELECT empID,
start_time,
date(start_time) AS date_only
FROM emp;
empid | start_time | date_only
-------+---------------------+------------
1 | 2024-09-23 04:15:00 | 2024-09-23
2 | 2024-04-01 04:05:00 | 2024-04-01
3 | 2024-10-26 07:00:00 | 2024-10-26
4 | 2024-12-14 09:15:00 | 2024-12-14
5 | 2024-12-07 12:56:00 | 2024-12-07
The resulting table contains a new column named date_only that contains only the date from the timestamp in the original start_time column.
Feel free to use similar syntax to extract the date from a timestamp column 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
