PostgreSQL: Update Date and Retain Time from Timestamp

Often in PostgreSQL you may want to update the date portion of a timestamp but retain the time.

You can use the following syntax to do so:

UPDATE emp 
SET start_time = start_time - start_time::date + '2024-01-01'::date;

This particular example updates the date portion of each timestamp in the start_time column of the emp table to be 2024-01-01 but retains the time portion.

Note: You can also replace ‘2024-01-01’::date with CURRENT_DATE to use the current date instead.

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

Example: Update Date and Retain Time from 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
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2022-09-23 04:15:23');
INSERT INTO emp VALUES (0002, '2023-04-01 04:05:56');
INSERT INTO emp VALUES (0003, '2024-10-26 07:00:10');
INSERT INTO emp VALUES (0004, '2024-12-14 09:15:00');
INSERT INTO emp VALUES (0005, '2024-12-07 12:56:30');

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

 empid |     start_time      
-------+---------------------
     1 | 2022-09-23 04:15:23
     2 | 2023-04-01 04:05:56
     3 | 2024-10-26 07:00:10
     4 | 2024-12-14 09:15:00
     5 | 2024-12-07 12:56:30

Suppose that we would like to update each timestamp in the start_time column to use 2024-01-01 as the date while keeping the time portion unchanged.

We can use the following syntax to do so:

-- update date portion of each timestamp in start_time column
UPDATE emp 
SET start_time = start_time - start_time::date + '2024-01-01'::date;

-- view updated TABLE
SELECT * FROM emp;

 empid |     start_time      
-------+---------------------
     1 | 2024-01-01 04:15:23
     2 | 2024-01-01 04:05:56
     3 | 2024-01-01 07:00:10
     4 | 2024-01-01 09:15:00
     5 | 2024-01-01 12:56:30

We can see that the date of each timestamp in the start_time column has been updated to 2024-01-01 while the time portion has remained unchanged.

We could also use the CURRENT_DATE function to update the date portion of each timestamp to the current date:

-- update date portion of each timestamp in start_time column
UPDATE emp 
SET start_time = start_time - start_time::date + CURRENT_DATE;

-- view updated TABLE
SELECT * FROM emp;

 empid |     start_time      
-------+---------------------
     1 | 2024-10-10 04:15:23
     2 | 2024-10-10 04:05:56
     3 | 2024-10-10 07:00:10
     4 | 2024-10-10 09:15:00
     5 | 2024-10-10 12:56:30

We can see that the date portion of each timestamp has been updated to 2024-10-10, which is the date when this article is currently being written.

Feel free to use whichever method you prefer.

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