i have trouble to get a parametrized range request on a timestamp column working. Small example in mclient:
create table test(creationDate timestamp); insert into test values('2012-04-02 00:00:00'), ('2012-03-30 00:00:00'),('2012-04-20 00:00:00');
-- works prepare select * from test where creationDate < ?; exec 21(TIMESTAMP '2012-04-01 00:00:00');
-- works select * from test where creationDate between TIMESTAMP '2012-04-01 00:00:00' and TIMESTAMP '2012-04-01 00:00:00' + interval '5' day;
-- syntax error, unexpected '?', expecting SCOLON prepare select * from test where creationDate between ? and ? + interval ? day;
-- fails too: program contains errors prepare select * from test where creationDate between ? and ? + interval '5' day; exec 22(TIMESTAMP '2012-04-01 00:00:00', TIMESTAMP '2012-04-01 00:00:00');
Does anybody know of a workaround? Is there a builtin function for adding days to a timestamp i missed? I couldn't find anything in the documentation. Btw, i'm on v11.23.3.
Just for the record, a possible workaround is: prepare select * from test where creationDate between ? and cast(? as timestamp) + cast(? as int) * interval '1' day;