PostgreSQL stored procedure data parameter

Question

I have the following stored procedure, which returns 0 results but if the run the query by itself it result lot of results. What am i missing.

CREATE OR REPLACE FUNCTION countStatistics(baselineDate Date) RETURNS int AS $$
DECLARE
    qty int;
BEGIN
    SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = baselineDate;
    RETURN qty;
END;
$$ LANGUAGE plpgsql;

--Execute the function
SELECT countStatistics('2015-01-01 01:00:00') as qty;

return 0 results

SELECT COUNT(*) FROM statistics WHERE time_stamp = '2015-01-01 01:00:00';

return 100+ results

Answer 1

You're declaring your baselineDate parameter as a date:

CREATE OR REPLACE FUNCTION countStatistics(baselineDate Date)

but using it as a timestamp:

SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = baselineDate;

You're getting an implicit cast so countStatistics('2015-01-01 01:00:00') will actually execute this SQL:

SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = '2015-01-01';

and, after the date is implicitly cast back to a timestamp, it will effectively be this:

SELECT COUNT(*) INTO qty FROM statistics WHERE time_stamp = '2015-01-01 00:00:00';

Try changing your function declaration to use a timestamp:

CREATE OR REPLACE FUNCTION countStatistics(baselineDate timestamp)