How to use the date range in PostgreSQL stored function?

Question

I am new to SQL. I am using PostgreSQL. I have created the stored function called report:

  CREATE OR REPLACE FUNCTION report(sensors VARCHAR,fromdate DATE,todate DATE) 
    RETURNS TABLE (
        sensor VARCHAR,
        id INT,
        value INT,
        created_date DATE
) 
AS $$
DECLARE 
    var_r record;
BEGIN
    FOR var_r IN(SELECT 
                        *
                FROM probe_data 
                WHERE probe_data.sensor =sensors AND probe_data.created_date >= fromdate AND  probe_data.created_date <=  todate )  
    LOOP
        sensor := var_r.sensor ; 
        id := var_r.id;
        created_date := var_r.created_date;
        value:= var_r.value;
        RETURN NEXT;
    END LOOP;
END; 

I want to get the data's between two date ranges.When I execute in SQL tool, it returns my desired output. But when I call the function

 let sql=  `select * from public.report($1,$2,$3);`
 let values=[req.body.sensors,req.body.fromdates,req.body.todates];

from my nodejs program, I am getting the data's out of range.

Here is my screen shot:

This is the structure of my return table

As you can see,In my second screen shot, I'm getting the data out of range. I don't where I am doing wrong.

Answer 1

Your parameter is declared as DATE but you are trying to pass a timestamp. So Postgres will cast the passed value to a date and you lose the time information. You need to define the parameter as timestamp if you want it to be treated as one. The return type of created_at should probably be a timestamp as well.

You can also get rid of the CURSOR loop and PL/pgSQL as well:

CREATE OR REPLACE FUNCTION report(sensors VARCHAR, fromdate timestamp, todate timestamp) 
    RETURNS TABLE (sensor VARCHAR, id INT, value INT, created_date timestamp) 
AS $$
  select pb.sensor, pb.id, pb.value, pg.created_date
  FROM probe_data 
  WHERE pb.sensor = sensors 
    AND pb.created_date >= fromdate 
    AND pb.created_date <= todate;
$$
language sql;