0

Where IFNULL(d.updated,d.inserted) < (CONCAT(YEAR(NOW()), "",MONTH(NOW)), "", "01") -INTERVAL ',date_logs,' DAY):"):

Question : I need to create a stored procedure that cab delete data from past 7 days the above codes is for 30 days only .please help im just new to sql thank you.

Stella
  • 19
  • 6

1 Answers1

0

The secret is in the usage of NOW() (converted to date to remove the current time of the day) and removing 7 days with interval '7 DAY'

the where condition should be similar to

Where IFNULL(d.updated,d.inserted) < NOW()::date - interval '7 DAY';
Ftisiot
  • 1,808
  • 1
  • 7
  • 13
  • 2
    `NOW()::date - interval '7 DAY'` can be simplified to `current_date - 7` –  Aug 24 '22 at 08:27
  • Thank you so much for the help but im getting an error when i tried to call it can you recheck my query IFNULL(d.updated,d.inserted) – Stella Aug 24 '22 at 13:13
  • And this is how I call the procedure CALL procedure_name('db_name',7) – Stella Aug 24 '22 at 13:16
  • Im getting the error : right syntax to use near ')-INTERVAL 7 DAY)' – Stella Aug 24 '22 at 13:24
  • Postgres does not have an `IFNULL` function/statement. What you want is *coalesce()* to choose the first non-null value from list. So: `coalesce(d.updated,d.inserted) < current_date -7` – Belayer Aug 24 '22 at 15:53
  • I just corrected the tagging it's mysql apologies. – Stella Aug 24 '22 at 16:44