Home > mailing lists

Thread: JDBC - Prepared statements and PostgreSql Time/Date operations

JDBC - Prepared statements and PostgreSql Time/Date operations

From

"Jan Bilek"

Date:

18 May 2007, 20:40:12

Hello,

I've got following problem:

I use this simple query:

select * from mytable where creation_time > (CURRENT_TIMESTAMP - interval '7 days');

---> it selects all rows from mytable, which were created before one week or sooner (creation_time is column in mytable).

I would like to use this query in java PreparedStatement, where age of a row would be one of its parameters:

PreparedStatement could look like this:

select * from mytable where creation_time > (CURRENT_TIMESTAMP - ?)

But nothing works with PreparedStatements!

I tried to set whole interval strings "interval '7 days'" as a parameter

I tried to set time strings '7 days' as a parameter

I tried to use BigDecimal as a parameter...

So, my question is: how to dynamically set time interval values to PreparedStatements and how to apply them to my example?

I've searched PostgreSql and Java docs, but i haven't find any cure.

Thanks for help.

Re: JDBC - Prepared statements and PostgreSql Time/Date operations

From

"Martin Gainty"

Date:

18 May 2007, 21:03:13

Hi Jan
how about casting the parameter to date format e.g.

select * from mytable where creation_time > (CURRENT_TIMESTAMP - $1::date)

Anyone?
M--

This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed. If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy. Thank you.

----- Original Message -----
From: Jan Bilek
To: pgsql-general@postgresql.org
Sent: Friday, May 18, 2007 4:39 PM
Subject: [GENERAL] JDBC - Prepared statements and PostgreSql Time/Date operations

Hello,

I've got following problem:

I use this simple query:

select * from mytable where creation_time > (CURRENT_TIMESTAMP - interval '7 days');

---> it selects all rows from mytable, which were created before one week or sooner (creation_time is column in mytable).

I would like to use this query in java PreparedStatement, where age of a row would be one of its parameters:

PreparedStatement could look like this:
select * from mytable where creation_time > (CURRENT_TIMESTAMP - ?)

But nothing works with PreparedStatements!
I tried to set whole interval strings "interval '7 days'" as a parameter
I tried to set time strings '7 days' as a parameter
I tried to use BigDecimal as a parameter...

So, my question is: how to dynamically set time interval values to PreparedStatements and how to apply them to my example?
I've searched PostgreSql and Java docs, but i haven't find any cure.

Thanks for help.

JB

Re: JDBC - Prepared statements and PostgreSql Time/Date operations

From

Tom Lane

Date:

18 May 2007, 21:26:52

"Jan Bilek" <bilekj@gmail.com> writes:
> I would like to use this query in java PreparedStatement, where age of a =
> row would be one of its parameters:

> PreparedStatement could look like this:
> select * from mytable where creation_time > (CURRENT_TIMESTAMP - ?)

> But nothing works with PreparedStatements!
> I tried to set whole interval strings "interval '7 days'" as a parameter
> I tried to set time strings '7 days' as a parameter
> I tried to use BigDecimal as a parameter...

I'm not sure that Java has a type corresponding to "interval".  Instead
use number-times-interval multiplication:

    ... creation_time > (CURRENT_TIMESTAMP - ? * interval '1 day')

The parameter is now of plain integer or float type.

            regards, tom lane

Re: JDBC - Prepared statements and PostgreSql Time/Date operations

From

"Jan Bilek"

Date:

18 May 2007, 22:16:07

Got it!
> "Jan Bilek" <bilekj@gmail.com> writes:
>> I would like to use this query in java PreparedStatement, where age of a
>> =
>> row would be one of its parameters:
>
>> PreparedStatement could look like this:
>> select * from mytable where creation_time > (CURRENT_TIMESTAMP - ?)
>
>> But nothing works with PreparedStatements!
>> I tried to set whole interval strings "interval '7 days'" as a parameter
>> I tried to set time strings '7 days' as a parameter
>> I tried to use BigDecimal as a parameter...
>
> I'm not sure that Java has a type corresponding to "interval".  Instead
> use number-times-interval multiplication:
>
> ... creation_time > (CURRENT_TIMESTAMP - ? * interval '1 day')

Integer-interval multiplication is the way!!!
Many thanks for quick help.

(Java seems not to have oracle/postgre interval type)

>
> The parameter is now of plain integer or float type.
>
> regards, tom lane