sql - PostgreSQL 日期差异

Question

我有一个计算日期差异的 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 的形式出现在变量中，这导致了问题。

如何减去变量中包含的日期？

Answer 1

调试

您的函数正在执行的操作可以简单得多 完成。语法错误的实际原因在这里:

SELECT EXTRACT(day FROM TIMESTAMP startDate - endDate) INTO diffDatePart;

看起来您正在尝试将 startDate 转换为 timestamp，这本来就是无稽之谈，因为您的参数 startDate 声明为时间戳 已经。

它也不起作用。我引用 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;

但这仍然没有多大意义。您正在谈论“日期”，但仍将您的参数定义为 timestamp。你可以像这样清理你拥有的东西:

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 只需要一次。
• date 列声明为正确类型 date。
• 不要使用大小写混合的标识符，除非您确切地知道自己在做什么。
• 从 end 中减去 start 以获得正数或应用 absolute value operator @ .

• 因为减去 dates(与减去 timestamps 相反，它产生一个 interval)已经产生 integer，简化为:

  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;

SQL函数

如果您坚持使用函数，请使用简单的 sql 函数:

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

PL/pgSQL 函数

如果你坚持要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$;

同样的调用。