2

I have a column called number_of_days_since_event and I want to change it to data_of_last_event, the way I can do that is subtracting today's date from the number of days in the number_of_days_since_event column. But I do not know how to subtract days in a column.

This question answers the problem when you know the number of days in advance, i.e., if I would like to subtract 10 days from today it would be:

SELECT CURRENT_DATE - INTERVAL '10 days';

However, I would like to do something like:

SELECT  CURRENT_DATE - INTERVAL  "myTable.number_of_days_since_event" 'days'
FROM myTable;

But this does not work leading to the error message: syntax error at or near "'day'"

zeh
  • 1,197
  • 2
  • 14
  • 29

2 Answers2

1

The following using concat solves my problem:

SELECT  CURRENT_DATE - concat(myTable.number_of_days_since_event::text,' day')::interval
FROM myTable;
zeh
  • 1,197
  • 2
  • 14
  • 29
1

If you are happy with a date result, you could use

SELECT current_date - number_of_days_since_event::integer
FROM mytable;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263