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?
Discussion: To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
Introduction to PostgreSQL NOW() function Note that the NOW() function returns current date and time based on the database server's time zone setting. As you can see, the value returned by the NOW() function is adjusted to the new timezone.
Posted on 23rd August 2022. YES, you can convert EPOCH to Timestamp by merely switching to the present Timestamp in PostgreSQL DBMS. EPOCH time is nothing but the number of seconds from 00:00:00 UTC on 1 January 1970. Till date, without adding the extra leap year days, this is considered.
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
.@
.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);
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;
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);
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With