如何在postgres中更新时间戳字段的一部分?

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 ?

示例:我的时间戳:2018-01-01 00:00.000表临时和字段临时日期。我想用SQL更新2018年到2016年。我怎么能呢?

2 个解决方案

#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:

如果你想设定一个确切的年份，例如，如果你的WHERE子句使用的不是年份，或者你根本没有使用WHERE子句:

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.

有关详细信息，请参阅邮件列表。

#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

上述更新将“2016”赋给表中的所有日期，其余日期部分(月日、小时等)保持不变

#1

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