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

From: M Q <thekaib(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: "where x between y and z" for timestamp data types
Date: 2012-08-12 18:52:22
Message-ID: CAGnP9ZgE8KFmO1hx6-Vjy6HCftKL=aBAgwnZumPBj9a8+Y2PZA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jeff Davis 2012-08-12 21:07:23 Re: "where x between y and z" for timestamp data types
Previous Message Csanyi Pal 2012-08-11 19:29:43 Re: To get a Table or View like a Calendar with dates