PostgreSQL date difference
Solution 1
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 typedate
. - 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 yieldsinteger
, 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.
Solution 2
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.
Solution 3
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;
Satish Sharma
I Am a Software Developer having an experience of 1 year in J2EE technologies. Past Work Experience of HCL and Presently working in Info-Objects Software's.
Updated on July 24, 2022Comments
-
Satish Sharma almost 2 years
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
andendDate
, which causes the problem.How can I subtract dates contained in variables?
-
Erwin Brandstetter over 11 yearsMy downvote is for the factually incorrect answer. Assignment with
SELECT INTO
works fine. There are a number of other problems here. -
fbynite over 11 years@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 11 yearsSorry, 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.