Postgres Pro
Downloads
Home   >   mailing lists

Thread: "where x between y and z" for timestamp data types

"where x between y and z" for timestamp data types

From
M Q
Date:
Hi,

I'm having trouble creating a function with a "where x between y and z" for timestamp data types.

If I hardcode the values for y and z, my function works fine and returns quickly (~80ms).  If I parameterize y and z (i.e. use $1, $2), then the function doesn't seem to return (killed query after waiting > 30 seconds)

Example:
I have two tables with timestamp data.

The hardcoded solution looks like this:

CREATE OR REPLACE FUNCTION time_test(IN timestamp without time zone, IN timestamp without time zone)
  RETURNS TABLE(v1 double precision, v2 double precision, ti1 timestamp without time zone, ti2 timestamp without time zone) AS
$BODY$
BEGIN
    RETURN QUERY 
    SELECT t1.value1, t2.value2, $1, $2
FROM 
MyTable1 as t1 inner join MyTable2 as t2 on t1.date = t2.date
WHERE t1.date between '2010-06-01 15:10:20' and '2010-06-01 15:10:20' ;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

select * from time_test('2010-06-01 15:10:20', '2010-06-01 15:10:20');  /* arguments here aren't really used internally by where clause */


The paramterized solution looks like this (Same as above but just using $1 and $2 in where clause):

CREATE OR REPLACE FUNCTION time_test(IN timestamp without time zone, IN timestamp without time zone)
  RETURNS TABLE(v1 double precision, v2 double precision, ti1 timestamp without time zone, ti2 timestamp without time zone) AS
$BODY$
BEGIN
    RETURN QUERY 
    SELECT t1.value1, t2.value2, $1, $2
FROM 
MyTable1 as t1 inner join MyTable2 as t2 on t1.date = t2.date
WHERE t1.date between $1 and $2;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

select * from time_test('2010-06-01 15:10:20', '2010-06-01 15:10:20');  /* arguments here should produce same result as hardcoded solution */


What am I not understanding?

Any help would be appreciated.

Thanks,
Kaib

Re: "where x between y and z" for timestamp data types

From
Jeff Davis
Date:
On Sun, 2012-08-12 at 11:52 -0700, M Q wrote:
> Hi,
>
>
> I'm having trouble creating a function with a "where x between y and
> z" for timestamp data types.

First, I'd like to suggest that you look at Range Types in the 9.2 beta,
which might be applicable to your problem:

http://www.postgresql.org/about/news/1405/
http://www.postgresql.org/docs/9.2/static/rangetypes.html
>
> If I hardcode the values for y and z, my function works fine and
> returns quickly (~80ms).  If I parameterize y and z (i.e. use $1, $2),
> then the function doesn't seem to return (killed query after waiting >
> 30 seconds)
>
>
> Example:
> I have two tables with timestamp data.

I briefly tried your example and I didn't see a problem. Can you provide
some sample data that illustrates your problem? Also, is the "date"
field a date or a timestamp? And why are the upper and lower bounds of
the BETWEEN identical?

Regards,
    Jeff Davis

Re: "where x between y and z" for timestamp data types

From
Tom Lane
Date:
Jeff Davis <pgsql@j-davis.com> writes:
> I briefly tried your example and I didn't see a problem. Can you provide
> some sample data that illustrates your problem? Also, is the "date"
> field a date or a timestamp? And why are the upper and lower bounds of
> the BETWEEN identical?

I'll bet a nickel the planner is choosing a different plan when it
doesn't know that the timestamp range condition is extremely selective.

            regards, tom lane

Re: "where x between y and z" for timestamp data types

From
M Q
Date:
The 'date' field is a timestamp without timezone.   The upper and lower bounds of the BETWEEN are identical just for testing purposes.  Any range would do.

I'm having trouble reproducing the problem on a similar data set.  I created a new db, same table schema, same row count with randomly generated data for testing but the function works fine.  So perhaps my problem is related to the tables rather than the function.  If I can successfully reproduce the problem with another data set I'll send example code to share.  I'll also look into the links you gave me.

Thanks,
Kaib




On Sun, Aug 12, 2012 at 2:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeff Davis <pgsql@j-davis.com> writes:
> I briefly tried your example and I didn't see a problem. Can you provide
> some sample data that illustrates your problem? Also, is the "date"
> field a date or a timestamp? And why are the upper and lower bounds of
> the BETWEEN identical?

I'll bet a nickel the planner is choosing a different plan when it
doesn't know that the timestamp range condition is extremely selective.

                        regards, tom lane