我有一个Postgresql函数来计算日期差异:
CREATE OR REPLACE FUNCTION testDateDiff () RETURNS int AS $BODY$ DECLARE startDate TIMESTAMP; DECLARE endDate TIMESTAMP; DECLARE diffDatePart int ; BEGIN Select evt_start_date From events Where evt_id = 5 INTO startDate ; Select evt_start_date From events Where evt_id = 6 INTO endDate ; SELECT EXTRACT(day FROM TIMESTAMP startDate - endDate) INTO diffDatePart; RETURN diffDatePart; END; $BODY$ LANGUAGE plpgsql COST 100
如果直接减去日期,则计算差异.但在我的情况下,日期在变量中出现为startDate和endDate,这会导致问题.
如何减去变量中包含的日期?
调试
SELECT EXTRACT(day FROM TIMESTAMP startDate - endDate) INTO diffDatePart;
看起来你正在尝试将startDate强制转换为时间戳,这开始时是无意义的,因为你的参数startDate已经被声明为timestamp.
它也行不通.我引用the manual here:
To avoid syntactic ambiguity,the type ‘string’ Syntax can only be
used to specify the type of a simple literal constant.
它会像这样工作:
SELECT EXTRACT(day FROM startDate - endDate)::int INTO diffDatePart;
但这仍然没有多大意义.您正在谈论“日期”,但仍将参数定义为时间戳.你可以清理你喜欢的东西:
CREATE OR REPLACE FUNCTION f_date_diff() RETURNS int AS $BODY$ DECLARE start_date date; end_date date; date_diff int; BEGIN SELECT evt_start_date FROM events WHERE evt_id = 5 INTO start_date; SELECT evt_start_date FROM events WHERE evt_id = 6 INTO end_date; date_diff := (endDate - startDate); RETURN date_diff; END $BODY$LANGUAGE plpgsql;
> DECLARE只需要一次.
>日期列声明为正确的类型日期.
>除非您确切知道自己在做什么,否则请勿使用大小写混合标识符.
>从结尾减去开头得到正数或应用absolute value operator @
.
>由于减去日期(而不是减去产生间隔的时间戳)已经产生整数,简化为:
SELECT (startDate - endDate) INTO diffDatePart;
或者更简单的plpgsql赋值:
diffDatePart := (startDate - endDate);
简单的查询
SELECT (SELECT evt_start_date FROM events WHERE evt_id = 6) - evt_start_date AS date_diff FROM events WHERE evt_id = 5;
或者你可以CROSS JOIN基表到自己(每个实例1行,所以没关系):
SELECT e.evt_start_date - s.evt_start_date AS date_diff FROM events e,events s WHERE e.evt_id = 6 AND s.evt_id = 5;
CREATE OR REPLACE FUNCTION f_date_diff(_start_id int,_end_id int) RETURNS int LANGUAGE sql AS $func$ SELECT e.evt_start_date - s.evt_start_date FROM events s,events e WHERE s.evt_id = $1 AND e.evt_id = $2 $func$;
呼叫:
SELECT f_date_diff(5,6);
如果你坚持使用plpgsql …
CREATE OR REPLACE FUNCTION f_date_diff(_start_id int,_end_id int) RETURNS int LANGUAGE plpgsql AS $func$ BEGIN RETURN (SELECT evt_start_date - (SELECT evt_start_date FROM events WHERE evt_id = _start_id) FROM events WHERE evt_id = _end_id); END $func$;
同样的电话.