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，这就导致了问题。

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

- Satish Sharma

---

如果你提供了这个函数定义不可避免产生的语法错误信息，那么就会更容易解决问题。即使对你来说这没有意义，但至少它可以帮助我们排除其他嫌疑。 - Erwin Brandstetter

Answer 1

调试

你的函数所做的事情可以用更简单的方法来完成。导致语法错误的实际原因在这里：

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

看起来你试图将startDate转换为timestamp，但这本来就是无意义的，因为你的参数startDate已经声明为timestamp。

而且它也不起作用。我在这里引用手册中的话：

  

为避免语法歧义，“string”类型的语法只能用于指定简单文字常量的类型。

它会像这样工作：

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 只需要使用一次。
• date 列声明为适当的类型 date。
• 不要使用混合大小写的标识符，除非您确切知道自己在做什么。
• 将 end 减去 start 以获得正数或使用绝对值运算符 @。

• 由于减去日期（而不是减去时间戳，它产生一个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$;

同一个电话。

- Erwin Brandstetter

Answer 2

我会这样编写查询语句：

create function testDateDiff()
  returns integer as $$
  declare 
    startDate timestamp;
    endDate timestamp;
  begin
    startDate := (select evt_start_date From events Where evt_id = 5);
    endDate   := (select evt_start_date From events Where evt_id = 6);
    return (select extract(day from startDate - endDate));
  end;
  $$ language 'plpgsql';

在上下文中使用:=和into的区别在于，使用:=时，您的查询必须返回单个值。如果您使用into，则您的查询可以返回单个行（即多个列）。

有关使用select和plpgsql的into的完整说明，请阅读http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html。具体来说，请参阅PostgreSQL文档的第39.5.3节。

- fbynite

---

我的负评是因为这个回答事实上是错误的。使用SELECT INTO进行赋值是可以正常工作的。这里还有其他一些问题。 - Erwin Brandstetter

@ErwinBrandstetter，如果您有时间，我还有哪些问题？这样我就能理解我哪里错了，如果您能告诉我，我将不胜感激。我现在明白为什么 OP 的 select into 语法没有问题了。 - fbynite

抱歉，我的上一条评论有歧义。我不是指你的回答有更多问题，而是指原帖中的代码有更多问题。我在我的回答中详细处理了这个问题。另外，由于你已经删除了错误信息，我现在已经取消了我的负评。 - Erwin Brandstetter

Answer 3

你真的需要为此编写一个函数吗？

这个查询也可以工作：

SELECT (SELECT evt_start_date::date FROM events WHERE evt_id = 5) 
        - evt_start_date::date 
        FROM events WHERE evt_id = 6;

- Borys