PostgreSQL日期差异 [英] PostgreSQL date difference

问题描述

我有一个PostgreSQL函数来计算日期差异：

 创建或替换函数testDateDiff（）RETURNS int AS $ BODY $ 
 DECLARE startDate TIMESTAMP; 
 DECLARE endDate TIMESTAMP; 
 DECLARE diffDatePart int; 
 BEGIN 
选择evt_start_date从事件where evt_id = 5 INTO startDate; 
选择evt_start_date从事件where evt_id = 6 INTO endDate; 
 SELECT EXTRACT（day FROM TIMESTAMP startDate  -  endDate）INTO diffDatePart; 
返回diffDatePart; 
 END; 
 $ BODY $ 
 LANGUAGE plpgsql 
成本100 
  

如果日期直接扣除差值计算。但是在我的情况下，日期是以 startDate 和 endDate 的变量存在，导致问题。

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

解决方案

调试

你的功能可以做的更简单很多。语法错误的实际原因是这里：

  SELECT EXTRACT（FROM FROM TIMESTAMP startDate  -  endDate）INTO diffDatePart; 
  

看起来您正在尝试投放 startDate 到 timestamp ，这是开始的废话，因为你的参数 startDate 被声明为时间戳已经。

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

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

它将这样工作：

  SELECT EXTRACT（day FROM startDate  -  endDate）:: int INTO diffDatePart; 
  

但这仍然不会很有意义。您正在谈论日期，但仍将您的参数定义为 timestamp 。您可以清除您所喜欢的内容：

 创建或替换功能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 / li>
  
• 不要使用混合大小写标识符，除非你确切知道你正在做什么。


• 从起始 结束以获得正数或应用绝对价值运算符 @ 。



• 从减去日期（而不是减去已经产生整数，简化为：

  >
  
  

    SELECT（startDate  -  endDate）INTO diffDatePart; 
    

  或者更简单的ppgsql分配：

    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 
，事件s 
 WHERE e.evt_id = 6 
 AND s.evt_id = 5; 
  

SQL函数

如果您坚持一个功能的目的，使用一个简单的sql函数：$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ b b b b b b b b b TION TION TION TION TION TION TION TION TION TION b b）） $ b 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 $;

致电：

 code> 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 
 
返回（SELECT evt_start_date 
  - （SELECT evt_start_date FROM events WHERE evt_id = _start_id）
 FROM events WHERE evt_id = _end_id ）; 
 END 
 $ func $; 
  

相同的电话。

I have a PostgreSQL function which calculates date difference:

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 startDate and endDate, which causes the problem.

How can I subtract dates contained in variables?

解决方案

Debug

What your function is doing could be done much simpler. 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 startDate to timestamp, which is nonsense to begin with, because your parameter startDate is declared as timestamp already.

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.

It would work 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 could sanitize what you have like this:

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 only needed once.
• date columns declared as proper type date.
• Don't use mixed case identifiers, unless you know exactly what you are doing.
• Subtract the start from the end to 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);
  

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 JOIN the base table to itself (1 row from each instance, so that's ok):

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

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

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

Call:

SELECT  f_date_diff(5, 6);

PL/pgSQL function

If you insist on 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.

这篇关于PostgreSQL日期差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！