Обсуждение: "where x between y and z" for timestamp data types
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
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
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
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'll bet a nickel the planner is choosing a different plan when it
> 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?
doesn't know that the timestamp range condition is extremely selective.
regards, tom lane