How to update a part of a timestamp field in postgres?

Question

Example: My timestamp: 2018-01-01 00:00:00.000 in table temp and field temp_date. I want to update 2018 to 2016 by SQL. How can I ?

Answer 1

UPDATE temp
SET temp_date = temp_date - interval '2 years'
WHERE EXTRACT (YEAR FROM temp_date) = 2018;

If you want to set it to an exact year, e.g. if your WHERE clause uses something other than the year, or if you're not using a WHERE clause at all:

UPDATE temp
SET temp_date = temp_date +
    (2016 - EXTRACT(YEAR FROM temp_date) || ' years')::interval
WHERE foo = 'bar';

For more details, refer to the mailing list.

Answer 2

Try:

UPDATE temp
   SET temp_date = temp_date - (date_trunc('year', temp_date ) - date '2016-1-1')

The above update assigns '2016' to all dates in the table,
leaving remaining date parts (month-day-hour etc.) unchanged