| From: | arons <arons7(at)gmail(dot)com> |
|---|---|
| To: | pgsql-jdbc(at)lists(dot)postgresql(dot)org |
| Subject: | JDBC setTimestamp question |
| Date: | 2023-01-24 13:52:29 |
| Message-ID: | CA+XOKQCfjisonmMzyz_RM6Q-==sYoRiZDQ=1avmsg6ffUXeB4A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-jdbc |
Dear All,
playing with postgres jdbc and timestamptz I found some confusing stuff.
Here an example of the code:
conn.prepareStatement(" drop table if exists test
").executeUpdate(); conn.prepareStatement(" create table if not
exists test(id bigint, pname text, create_dt timestamptz) ")
.executeUpdate(); System.out.println("insert some
data.."); conn.prepareStatement(" insert into test values( 1,
'hello', now() - interval '1 day' ) ").executeUpdate();
conn.prepareStatement(" insert into test values( 2, 'world', now() -
interval '1 day' ) ").executeUpdate();
System.out.println("Executing query 01 ..."); try
(PreparedStatement ps01 = conn.prepareStatement("select * from test
where create_dt < ? ");) { ps01.setTimestamp(1, new
Timestamp(System.currentTimeMillis()), null); ResultSet rs
= ps01.executeQuery(); while (rs.next()) {
System.out.println("query 01 id:" + rs.getInt("id")); }
} System.out.println("Executing query 02 ..."); try
(PreparedStatement ps02 = conn
.prepareStatement("select * from test where create_dt < ? +
interval '1 day' ");) { ps02.setTimestamp(1, new
Timestamp(System.currentTimeMillis()), null); ResultSet rs
= ps02.executeQuery(); while (rs.next()) {
System.out.println("query 02 id:" + rs.getInt("id")); }
}
the first select run without any problem, the second query instead:
select * from test where create_dt < ? + interval '1 day'
gives this error:
org.postgresql.util.PSQLException: ERROR: operator does not exist:
timestamp with time zone < interval
My first question is, how is the proper way to set the parameter to be of
type timestamptz? if I add cast(? as timestamptz) all work fine.
NOTE the following query run without problem in postgres: select * from
test where create_dt < now() + interval '1 day'
So I expected the same from jdbc setting the paramter with method
setTimestamp.
I've tried also the following:
System.out.println("Executing query 00 ..."); try
(PreparedStatement ps02 = conn.prepareStatement(" select ? as tt ");)
{ ps02.setTimestamp(1, new
Timestamp(System.currentTimeMillis()), null); ResultSet rs
= ps02.executeQuery(); ResultSetMetaData metadata =
rs.getMetaData(); System.out.println("column type : " +
metadata.getColumnType(1)); System.out.println("varchar ?
:" + java.sql.Types.VARCHAR); while (rs.next()) {
System.out.println("tt:" + rs.getString(1));
System.out.println("tt:" + rs.getTimestamp(1)); } }
Why in this case the data type is set to varchar?
I was really surprise from the result.
That exaplain also to me why the query above give me the error. Some how
the expression with varchar and interval results in a interval type?
How can I deal with those problems?
Is there any documentation abot jdbc set method and their result db types?
I was not able to find.
Thanks for help
Renzo
| From | Date | Subject | |
|---|---|---|---|
| Next Message | arons | 2023-01-25 13:04:39 | Re: JDBC setTimestamp question |
| Previous Message | Dave Cramer | 2023-01-18 21:30:39 | [pgjdbc/pgjdbc] 3fdc2e: add ssl redirect (#2735) |