PostgreSQL date difference

Question

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?

If you had provided the syntax error message that you inevitably get for this function definition, it would have been easier. Even if it doesn't make sense for you, it at least helps us rule out other suspects. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 30, 2012 at 15:46

Erwin Brandstetter · Accepted Answer · 2013-08-12 07:42:35Z

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.

fbynite · Accepted Answer · 2012-12-30 22:00:23Z

1

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 into in the context above is that using := your query must return a single value. If you use into your query can return a single row (i.e. more than one column).

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

edited Dec 30, 2012 at 22:00

answered Dec 29, 2012 at 0:09

fbynite

2,6611 gold badge22 silver badges26 bronze badges

3 Comments

Erwin Brandstetter Over a year ago

My downvote is for the factually incorrect answer. Assignment with SELECT INTO works fine. There are a number of other problems here.

fbynite Over a year ago

@ErwinBrandstetter, If you have time, what are my other problems? So I can understand where else I wrong, I would appreciate it. I see now why the OP's syntax for the select into wasn't wrong.

Erwin Brandstetter Over a year ago

Sorry, my last remark was ambiguous. I didn't mean more problems in your answer, but more problems in the OP's code. I have dealt with that extensively in my answer. Also, removed my downvote now since you removed the misinformation.

Borys · Accepted Answer · 2012-12-31 05:06:10Z

1

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;

answered Dec 31, 2012 at 5:06

Borys

3,0443 gold badges28 silver badges38 bronze badges

Collectives™ on Stack Overflow

PostgreSQL date difference

3 Answers 3

Debug

Simple query

SQL function

PL/pgSQL function

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Debug

Simple query

SQL function

PL/pgSQL function

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related