PostgreSQL date difference

Question

I have a PostgreSQL function that calculates the 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 the dates are subtracted directly, the difference is calculated. But in my case, dates are present in variables like startDate and endDate , which causes a problem.

How can I subtract dates contained in variables?

Answer 1

Debugging

What your function does can be made much simpler. The actual reason for the syntax error is:

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

It looks like you are trying to drop startDate in timestamp , which is pointless to start because your startDate parameter startDate already declared as timestamp .

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

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

It will work as follows:

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

But that still doesn't make much sense. You talk about “dates,” but you still define your parameters as a timestamp . You can sanitize what you have:

 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 is required only once.
• date columns declared as the correct date type.
• Do not use mixed case identifiers unless you know exactly what you are doing.
• Subtract the beginning from the end to get a positive number, or use the absolute value operator @ .

• Since subtracting dates (as opposed to subtracting timestamps that interval gives) already gives integer , simplify:

   SELECT (startDate - endDate) INTO diffDatePart; 

  Or even simpler than plpgsql assignment:

   diffDatePart := (startDate - endDate); 

Simple request

You can solve a simple problem 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 can CROSS JOIN base table for yourself (1 row from each instance, so fine):

 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 functions for this 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$; 

The same challenge.

Answer 2

I would write the query as follows:

 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 above context is that when using := your query should return a single value. If you use into , your query may 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 . In particular, section 39.5.3 of the PostgreSQL documentation.

Answer 3

Do you really need a function for this?

This query will also work:

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