Difference between two dates in postgresql

Question

Function:

CREATE FUNCTION diff(d1 date,d2 date) RETURNS int AS $$
BEGIN
IF d1 = NULL THEN
RETURN SELECT extract(year from age(current_date,d2));
ELSE
RETURN SELECT extract(year from age(d1,d2));
END IF;
END
$$ language plpgsql;

My requirement is to find the difference between two dates in years. So, I write the above function. Here, if d1 is NULL then it is assigned with current date. But, it produce error like as shown below.

ERROR:  syntax error at or near "SELECT"
LINE 1: SELECT  SELECT extract(year from age(current_date, $1 ))
QUERY:  SELECT  SELECT extract(year from age(current_date, $1 ))
CONTEXT:  SQL statement in PL/PgSQL function "diff" near line 4

Does any one help me to solve this problem.

did you try removing SELECT ? RETURN extract(year from age(d1,d2)); — Jay Kumar R
– Jay Kumar R, Commented Feb 3, 2016 at 5:16
Other than Jay's comment, another potential problem might be that AGE() is not returning a date type which extract() can handle. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 3, 2016 at 5:18
@JayKumarR As per your suggestion, the function created successfully. But, it doesn't give the expected output. If d1 is null, it returns NULL as output. — mohangraj
– mohangraj, Commented Feb 3, 2016 at 5:31

Abhishek · Accepted Answer · 2016-02-03 05:44:37Z

1

Try :

date_part('year',age(coalesce(d1,current_date), d2))::int;

age(d1,d2) function returns the number of years, months, and days between two dates in following format:

xxx year(s) xxx mon(s) xxx day(s).

from this output using date_part() to pick the only the year difference. and also no need to put if statement to handle NULL as I have added coalesece which returns first NON Null value, So if d1 is NULL it return cuurent_date

Function Structure:

CREATE OR REPLACE FUNCTION diff(d1 date,d2 date) RETURNS int AS $$
BEGIN

 RETURN date_part('year',age(coalesce(d1,current_date), d2))::int;
END
$$ language plpgsql;

Function Call:

select * from diff(null,'2010-04-01');
select * from diff('2012-10-01','2010-04-01');

edited Feb 3, 2016 at 5:44

answered Feb 3, 2016 at 5:24

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mohangraj Over a year ago

It shows error like 'ERROR: mismatched parentheses at or near ")" LINE 7: RETURN date_part('year',age(d1,current_date), d2))::int; ^

Abhishek Over a year ago

share the function you have created.

Abhishek Over a year ago

This is working for mine. Please check the updated answer.

mohangraj Over a year ago

CREATE FUNCTION diff(d1 date,d2 date) RETURNS int AS $$             BEGIN     IF d1 = NULL THEN     RETURN extract(year from age(current_date,d2));     ELSE     RETURN extract(year from age(d1,d2));     END IF;     END     $$ language plpgsql;

Abhishek Over a year ago

@mrg: Create the function as in answer and No need to check for NULL value.

Collectives™ on Stack Overflow

Difference between two dates in postgresql

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related