Often in PostgreSQL you may want to combine a date column and a time column into a single timestamp column.
You can use the following basic syntax to do so:
SELECT *,
(start_date + start_time ) AS start_datetime
FROM emp;
This particular example combines the start_date and start_time columns from the emp table and saves the results to a new column named start_datetime.
Note: When combining a date and time column, you must use the + operator instead of the || operator.
The following example shows how to use this syntax in practice.
Example: How to Combine Date and Time Columns in PostgreSQL
Suppose that we create the following table named emp that contains information about when various employees started working on some specific task:
-- create table named 'emp'
CREATE TABLE emp (
empID INT,
start_date DATE,
start_time TIME
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2024-09-01', '07:34:00');
INSERT INTO emp VALUES (0002, '2024-09-01', '08:38:00');
INSERT INTO emp VALUES (0003, '2024-09-01', '11:45:00');
INSERT INTO emp VALUES (0004, '2024-09-01', '12:02:00');
INSERT INTO emp VALUES (0005, '2024-09-01', '14:58:00');
-- view all rows in table
SELECT * FROM emp;
empid | start_date | start_time
-------+------------+------------
1 | 2024-09-01 | 07:34:00
2 | 2024-09-01 | 08:38:00
3 | 2024-09-01 | 11:45:00
4 | 2024-09-01 | 12:02:00
5 | 2024-09-01 | 14:58:00
Suppose that we would like to combine the date from the start_date column with the time in the start_time column.
We can use the following syntax to do so:
SELECT *,
(start_date + start_time ) AS start_datetime
FROM emp;
empid | start_date | start_time | start_datetime
-------+------------+------------+---------------------
1 | 2024-09-01 | 07:34:00 | 2024-09-01 07:34:00
2 | 2024-09-01 | 08:38:00 | 2024-09-01 08:38:00
3 | 2024-09-01 | 11:45:00 | 2024-09-01 11:45:00
4 | 2024-09-01 | 12:02:00 | 2024-09-01 12:02:00
5 | 2024-09-01 | 14:58:00 | 2024-09-01 14:58:00
The resulting table contains a new column named start_datetime that combines the date and time values from the start_date and start_time columns, respectively.
You can use similar syntax to combine date and time columns into a single column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate Difference Between Dates in Days
PostgreSQL: How to Calculate Difference Between Dates in Weeks
PostgreSQL: How to Calculate Difference Between Dates in Months
