0

Im using postgres as my database and I have a table that has a date column. The current value in this column is 2021-04-1 17:19:08

I want to update this column's value and move the date 20 (or any other amount of days) ahead so the new value will be 2021-04-20 17:19:08

the reason Im not doing it manually its because I have too much rows to move ahead so I need a query for that, and I need to freedom to choose the amount of days ahead

Thanks in advance

Tal Levi
  • 363
  • 1
  • 6
  • 22

2 Answers2

2

You can add days using INTERVAL -

SELECT CURRENT_DATE + INTERVAL '20 days';
Nikhil Patil
  • 2,480
  • 1
  • 7
  • 20
0

With the help of Nikhil Patil I managed to create the next query:

select 'UPDATE itpserver.managed_incidents SET trigger_time = ''' || trigger_time || ''' where id = ' || id::text || ';'

from (

      select id, trigger_time + INTERVAL '120 days' trigger_time

      FROM itpserver.managed_incidents 

) a

where 1=1

group by id,trigger_time

order by id

this query created all the update queries I needed in order to update all the rows in my table. (the trigger_time is the date column).

after it created all the update queries I needed, I ran all of them at the same time as a sql script

Tal Levi
  • 363
  • 1
  • 6
  • 22
  • 1
    There is no need to _generate_ those queries. You can directly update the table using `update itpserver.managed_incidents set trigger_time = trigger_time + interval '120 days'` directly. –  Aug 19 '21 at 14:50
  • Yes you are right... I complicated it too much :( – Tal Levi Aug 20 '21 at 17:09