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 ?
Add a comment
|
2 Answers
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.
5 Comments
Nguyễn Thắng
How can if we wanna adding some years ?
Dan Walmsley
I'd imagine, UPDATE temp SET temp_date = temp_date + interval '2 years' WHERE EXTRACT (YEAR FROM temp_date) = 2018;
Patrick
You can use an
interval to add or subtract any amount of time from a timestamp value, even like interval '2 years 5 months -1 day 12 hours 4 seconds'.Nguyễn Thắng
sorry because I been tried + interval but my data was updated to NULL. So i think has something wrong !
Nguyễn Thắng
I think I forgot something, I try again and it work, thanks @Patrick
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
5 Comments
Patrick
This does not address the question. Besides, you update all rows, even those where the year is already 2016, and that is very inefficient on a large table. And what about
temp_dates in 2014?
krokodilko
@Patrick, you are absolutely right, this is very very very inefficient. But everyone can very easily solve this problem using simple
WHERE condition. Regarding 2014 - it also is "replaced" with 2016, please play with this query and you will see.Patrick
If there is a simple solution already, then why add an inefficient one which has side effects not asked for?
krokodilko
@Patrick you citicise others and blame them for unefficiency, but you can't see in your own answer that your
WHERE EXTRACT (YEAR FROM temp_date) = 2018; is unable to use an index and always must do a full table scan.Patrick
The question is about updating a number of rows, not something that would conceivably happen very often so an index would probably mean more overhead (in building and maintaining) than benefit in this case. Other than that, an index can be built on the expression. But index issues aside, it is far better to have a solution that actually works than one that very slowly does the wrong thing. Your solution would not use an index either because it has to update all rows, in the process making the table twice as big as before.