CODEWITHSUNDEEP
postgresql
zeh
zeh

Reputation: 1317

How to subtract a number of days in one column from CURRENT_DATE in PostgreSQL

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'"

Upvotes: 3

Views: 2257

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247665

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

SELECT current_date - number_of_days_since_event::integer
FROM mytable;

Upvotes: 2

zeh
zeh

Reputation: 1317

The following using concat solves my problem:

SELECT  CURRENT_DATE - concat(myTable.number_of_days_since_event::text,' day')::interval
FROM myTable;

Upvotes: 2

Related Questions