CODEWITHSUNDEEP
sqlpostgresqldate
Lucius Rutilius Lupus
Lucius Rutilius Lupus

Reputation: 169

How to provide a date variable an interval that consists of an integer variable in Postgresql

I am trying to extract an amount of years from a specific date for this the correct syntax is

<date> - interval '5 years';

But I dont want to extract a specific amount of years but a variable, which user will provide as a parameter. I have tried the following the variable name is years :

date+interval '% years',years;

I am getting an error and it doesn't let me do it that way. What would be the right way to do it.

Upvotes: 1

Views: 478

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324275

ruakh is right; you can use string concatenation to create an interval literal then cast it.

I prefer to use date maths to create the interval, rather than string concatenation. It is, IMO, cleaner.

SELECT DATE '2012-01-01' + 10 * INTERVAL '1' YEAR;

Here, you can replace 10 with the desired number of years, or a placeholder parameter.

Upvotes: 0

ruakh
ruakh

Reputation: 183201

I believe you should be able to write:

specific_date + (number_of_years || ' years')::INTERVAL

(Disclaimer: not tested, and it's been more than a year since I last did this sort of thing.)

Upvotes: 2

Related Questions