PostgreSQL中两个日期之间的差异 [英] Difference between two dates in postgresql

问题描述

功能：

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;

我的要求是找出年份中两个日期之间的差。所以，我写了上面的函数。在此，如果d1为NULL，则将其分配为当前日期。但是，它会产生如下所示的错误。

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.

推荐答案

尝试：

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

age（d1，d2）函数返回以下格式的两个日期之间的年，月和日的数量：

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).

从此输出使用 date_part（）选择唯一的年份差异。并且也不需要放置if语句来处理 NULL ，因为我添加了 coalesece ，该返回第一个 NON Null 值，因此，如果 d1 为 NULL ，则返回 cuurent_date

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

功能结构：

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;

函数调用：

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

这篇关于PostgreSQL中两个日期之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！