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屋!