PostgreSQL 日期差异

PostgreSQL date difference

提问by Satish Sharma

I have a PostgreSQL function which calculates date difference:

我有一个计算日期差异的 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

If dates are subtracted directly then difference is calculated. But in my case dates are present in variables as startDateand endDate, which causes the problem.

如果直接减去日期，则计算差异。但在我的情况下，日期作为startDateand存在于变量中endDate，这会导致问题。

How can I subtract dates contained in variables?

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

回答by Erwin Brandstetter

Debug

调试

What your function is doing could be done muchsimpler. The actual cause for the syntax error is here:

您的功能正在做的事情可以做得更简单。语法错误的实际原因在这里：

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

It looks like you are trying to cast startDateto timestamp, which is nonsense to begin with, because your parameter startDateis declared as timestampalready.

看起来您正在尝试强制转换startDate为timestamp，这是无稽之谈，因为您的参数startDate已声明为timestamp已经。

It also does not work. I quote the manual here:

它也不起作用。我在这里引用手册：

To avoid syntactic ambiguity, the type 'string' syntax can only be used to specify the type of a simple literal constant.

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

It wouldwork like this:

它会像这样工作：

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

But that still wouldn't make a lot of sense. You are talking about "dates", but still define your parameters as timestamp. You couldsanitize what you have like this:

但这仍然没有多大意义。您在谈论“日期”，但仍将您的参数定义为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;

• DECLAREonly needed once.
• datecolumns declared as proper type date.
• Don't use mixed case identifiers, unless you know exactly what you are doing.
• Subtract the startfrom the endto get a positive number or apply the absolute value operator @.

• Since subtracting dates(as opposed to subtracting timestamps, which yields an interval) already yields integer, simplify to:

  SELECT (startDate - endDate) INTO diffDatePart;
  

  Or even simpler as plpgsql assignment:

  diffDatePart := (startDate - endDate);
  

• DECLARE只需要一次。
• date声明为正确类型的列date。
• 不要使用大小写混合的标识符，除非您确切地知道自己在做什么。
• 从末尾减去开始以获得正数或应用绝对值运算符。@

• 由于减去日期（而不是减去时间戳，它会产生一个interval）已经产生了integer，简化为：

  SELECT (startDate - endDate) INTO diffDatePart;
  

  或者更简单的 plpgsql 赋值：

  diffDatePart := (startDate - endDate);
  

Simple query

简单查询

You can solve the simple task with a simple query - using a subquery:

您可以使用简单的查询来解决简单的任务 - 使用子查询：

SELECT (SELECT evt_start_date
        FROM   events
        WHERE  evt_id = 6) 
      - evt_start_date AS date_diff
FROM   events
WHERE  evt_id = 5;

Or you could CROSS JOINthe base table to itself (1 row from each instance, so that's ok):

或者您可以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 function

SQL函数

If you insist on a function for the purpose, use a simple sql function:

如果你坚持一个函数为目的，使用一个简单的 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 = 
AND    e.evt_id = 
$func$;

Call:

称呼：

SELECT  f_date_diff(5, 6);

PL/pgSQL function

PL/pgSQL 函数

If you insist on plpgsql ...

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

Same call.

同样的电话。

回答by fbynite

I would write the query like this:

我会写这样的查询：

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

The difference between using :=and intoin the context above is that using :=your query must return a single value. If you use intoyour query can return a single row (i.e. more than one column).

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

For a full explanation of using selectwith intoand plpgsql you should read http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html. Specifically, section 39.5.3 of the PostgreSQL documentation.

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

回答by Borys

Do you really need a function for this?

你真的需要一个函数吗？

This query would work as well:

这个查询也可以工作：

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