Postgres Timestamp between not selecting records for function

Question

Postgres 9.6

I have a function that utilizes a BETWEEN operator to select records which were created within a specified date range. This is an example of my structure and function

table

CREATE TABLE test.dated_records (
    dated_record_id int4 NOT NULL DEFAULT nextval('test.dated_record_dated_record_id_seq'::regclass),
    create_date timestamptz NOT NULL DEFAULT now(),
    update_date timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT dated_record_pkey PRIMARY KEY (dated_record_id),
)
WITH (
    OIDS=FALSE
) ;

function

  CREATE OR REPLACE FUNCTION test.list_date_records(_start_date timestamp with time zone, _end_date timestamp with time zone,)
 RETURNS TABLE(dated_record_id integer, create_date timestamptz,update_date timestamptz )
 LANGUAGE plpgsql
AS $function$
  BEGIN
        return QUERY EXECUTE
            $q$
                SELECT              
                  dr.dated_record_id,
                  dr.create_date,
                  dr.update_date
                FROM test.dated_records dr
                WHERE dr.create_date BETWEEN $1 AND $2
                ORDER BY dr.create_date DESC
            $q$        
            using _start_date, _end_date;
       return;
    END
$function$

I have a record with a create_date of '2017-07-06 21:55:40.550468' and the logs are recording my parameters as

LOG:  execute <unnamed>: 

                  SELECT * FROM test.list_date_records($1, $2);

DETAIL:  parameters: $1 = '2017-06-08 00:00:00', $2 = '2017-07-06 23:59:59'

Yet i get nothing back. I've tried altering everything to plain TIMESTAMP and casting the create_date to DATE but nothing seems to work.

I'm sure it has something to with my timezone (I'm EST - New York) but I can't figure out what it is for the life of me.

I'm hoping that someone can point out what I'm doing wrong.

Thanks in advance.

Answer 1

Here the solution:

Sample Data:

stackoverflow=# select * from test.dated_records ;  

 dated_record_id |      create_date       |          update_date       
--------------------+--------------------+--------------------------------        
1 | 2017-06-08 00:00:00+07 | 2017-07-07 10:41:43.755836+07  
2 | 2017-07-06 23:59:59+07 | 2017-07-07 10:41:43.828373+07  
3 | 2017-08-06 23:59:59+07 | 2017-07-07 10:41:43.840404+07     
4 | 2017-09-06 23:59:59+07 | 2017-07-07 10:41:43.851223+07

(4 rows)

Function

CREATE OR REPLACE FUNCTION test.list_date_records(_start_date timestamp with time zone, _end_date timestamp with time zone)
 RETURNS TABLE (
 dated_record_id integer, -- same as selected column datatype
 create_date timestamptz, -- same as selected column datatype 
 update_date timestamptz  -- same as selected column datatype
 ) 
 AS $function$
 BEGIN
        return QUERY EXECUTE
            $q$
                SELECT              
                  dr.dated_record_id,
                  dr.create_date,
                  dr.update_date
                FROM test.dated_records dr
                WHERE dr.create_date BETWEEN $1 AND $2
                ORDER BY dr.create_date DESC
            $q$        
            using _start_date, _end_date;
       return;
    END
$function$
LANGUAGE plpgsql
SECURITY DEFINER;

Execute

stackoverflow=# SELECT * FROM test.list_date_records('2017-06-08 00:00:00', '2017-07-06 23:59:59');
 dated_record_id |      create_date       |          update_date          
-----------------+------------------------+-------------------------------
               2 | 2017-07-06 23:59:59+07 | 2017-07-07 10:41:43.828373+07
               1 | 2017-06-08 00:00:00+07 | 2017-07-07 10:41:43.755836+07
(2 rows)