public inbox for [email protected]
help / color / mirror / Atom feedJDBC setTimestamp question
8+ messages / 2 participants
[nested] [flat]
* JDBC setTimestamp question
@ 2023-01-24 13:52 arons <[email protected]>
2023-01-25 13:04 ` Re: JDBC setTimestamp question arons <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: arons @ 2023-01-24 13:52 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: JDBC setTimestamp question
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
@ 2023-01-25 13:04 ` arons <[email protected]>
2023-01-25 15:07 ` Re: JDBC setTimestamp question Dave Cramer <[email protected]>
2023-01-25 15:14 ` Re: JDBC setTimestamp question arons <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: arons @ 2023-01-25 13:04 UTC (permalink / raw)
To: [email protected]
I saw the code is not well formatted, at least if you read from the web
site of the mailing list.
The source code you can download here:
https://kdani.ch/share/pg/jdbc/TestTimestamptz.java.
How do you generally format code on the mailing list?
Thanks
On Tue, Jan 24, 2023 at 2:52 PM arons <[email protected]> wrote:
> 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
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: JDBC setTimestamp question
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
2023-01-25 13:04 ` Re: JDBC setTimestamp question arons <[email protected]>
@ 2023-01-25 15:07 ` Dave Cramer <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Dave Cramer @ 2023-01-25 15:07 UTC (permalink / raw)
To: arons <[email protected]>; +Cc: [email protected]
On Wed, 25 Jan 2023 at 08:05, arons <[email protected]> wrote:
> I saw the code is not well formatted, at least if you read from the web
> site of the mailing list.
> The source code you can download here:
> https://kdani.ch/share/pg/jdbc/TestTimestamptz.java.
> How do you generally format code on the mailing list?
> Thanks
>
>
>
> On Tue, Jan 24, 2023 at 2:52 PM arons <[email protected]> wrote:
>
>> 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
>>
>
This isn't really surprising and has nothing to do with JDBC. If you tried
this in psql you would get the same result. One thing to keep in mind is
that JDBC uses prepared statements so you see the following:
prepare foo as select * from test where create_dt < $1 + interval '1
day';
ERROR: operator does not exist: timestamp with time zone < interval
LINE 1: ...epare foo as select * from test where create_dt < $1 + in...
^
HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.
test=# prepare foo as select * from test where create_dt <
$1::timestamptz + interval '1 day';
Does that help ?
Dave
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: JDBC setTimestamp question
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
2023-01-25 13:04 ` Re: JDBC setTimestamp question arons <[email protected]>
@ 2023-01-25 15:14 ` arons <[email protected]>
2023-01-25 15:19 ` Re: JDBC setTimestamp question Dave Cramer <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: arons @ 2023-01-25 15:14 UTC (permalink / raw)
To: [email protected]
I checked the source code and inside the method
org.postgresql.jdbc.PgPreparedStatement.setTimestamp(int, Timestamp,
Calendar) I found the code I was interesting to:
int oid = Oid.UNSPECIFIED;
// Use UNSPECIFIED as a compromise to get both TIMESTAMP and TIMESTAMPTZ
working.
// This is because you get this in a +1300 timezone:
//
// template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;
// timestamptz
// ------------------------
// 2005-01-01 18:00:00+13
// (1 row)
// template1=# select '2005-01-01 15:00:00 +1000'::timestamp;
// timestamp
// ---------------------
// 2005-01-01 15:00:00
// (1 row)
// template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
// timestamp
// ---------------------
// 2005-01-01 18:00:00
// (1 row)
// So we want to avoid doing a timestamptz -> timestamp conversion, as that
// will first convert the timestamptz to an equivalent time in the server's
// timezone (+1300, above), then turn it into a timestamp with the "wrong"
// time compared to the string we originally provided. But going straight
// to timestamp is OK as the input parser for timestamp just throws away
// the timezone part entirely. Since we don't know ahead of time what type
// we're actually dealing with, UNSPECIFIED seems the lesser evil, even if
it
// does give more scope for type-mismatch errors being silently hidden.
// If a PGTimestamp is used, we can define the OID explicitly.
if (t instanceof PGTimestamp) {
PGTimestamp pgTimestamp = (PGTimestamp) t;
if (pgTimestamp.getCalendar() == null) {
oid = Oid.TIMESTAMP;
} else {
oid = Oid.TIMESTAMPTZ;
cal = pgTimestamp.getCalendar();
}
}
if (cal == null) {
cal = getDefaultCalendar();
}
bindString(i, getTimestampUtils().toString(cal, t), oid);
I saw that I can use PGTimestamp instead of sql Timestamp.
In that case all works fine.
Anyway I do not fully understand the comment and why we set oid = Oid.
UNSPECIFIED; in case of normal sql Timestamp.
Is there any different between a java.sql.Timestamp.Timestamp and
org.postgresql.util.PGTimestamp.PGTimestamp ?
Cannot be use oid = Oid.TIMESTAMP in any other case as when t is not an
instance of PGTimestamp?
Thanks
Renzo
On Wed, Jan 25, 2023 at 2:04 PM arons <[email protected]> wrote:
> I saw the code is not well formatted, at least if you read from the web
> site of the mailing list.
> The source code you can download here:
> https://kdani.ch/share/pg/jdbc/TestTimestamptz.java.
> How do you generally format code on the mailing list?
> Thanks
>
>
>
> On Tue, Jan 24, 2023 at 2:52 PM arons <[email protected]> wrote:
>
>> 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
>>
>>
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: JDBC setTimestamp question
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
2023-01-25 13:04 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-25 15:14 ` Re: JDBC setTimestamp question arons <[email protected]>
@ 2023-01-25 15:19 ` Dave Cramer <[email protected]>
2023-01-26 09:57 ` Re: JDBC setTimestamp question arons <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Dave Cramer @ 2023-01-25 15:19 UTC (permalink / raw)
To: arons <[email protected]>; +Cc: [email protected]
On Wed, 25 Jan 2023 at 10:15, arons <[email protected]> wrote:
> I checked the source code and inside the method
> org.postgresql.jdbc.PgPreparedStatement.setTimestamp(int, Timestamp,
> Calendar) I found the code I was interesting to:
>
> int oid = Oid.UNSPECIFIED;
>
> // Use UNSPECIFIED as a compromise to get both TIMESTAMP and TIMESTAMPTZ
> working.
>
> // This is because you get this in a +1300 timezone:
>
> //
>
> // template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;
>
> // timestamptz
>
> // ------------------------
>
> // 2005-01-01 18:00:00+13
>
> // (1 row)
>
> // template1=# select '2005-01-01 15:00:00 +1000'::timestamp;
>
> // timestamp
>
> // ---------------------
>
> // 2005-01-01 15:00:00
>
> // (1 row)
>
> // template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
>
> // timestamp
>
> // ---------------------
>
> // 2005-01-01 18:00:00
>
> // (1 row)
>
> // So we want to avoid doing a timestamptz -> timestamp conversion, as
> that
>
> // will first convert the timestamptz to an equivalent time in the
> server's
>
> // timezone (+1300, above), then turn it into a timestamp with the "wrong"
>
> // time compared to the string we originally provided. But going straight
>
> // to timestamp is OK as the input parser for timestamp just throws away
>
> // the timezone part entirely. Since we don't know ahead of time what type
>
> // we're actually dealing with, UNSPECIFIED seems the lesser evil, even if
> it
>
> // does give more scope for type-mismatch errors being silently hidden.
>
> // If a PGTimestamp is used, we can define the OID explicitly.
>
> if (t instanceof PGTimestamp) {
>
> PGTimestamp pgTimestamp = (PGTimestamp) t;
>
> if (pgTimestamp.getCalendar() == null) {
>
> oid = Oid.TIMESTAMP;
>
> } else {
>
> oid = Oid.TIMESTAMPTZ;
>
> cal = pgTimestamp.getCalendar();
>
> }
>
> }
>
> if (cal == null) {
>
> cal = getDefaultCalendar();
>
> }
>
> bindString(i, getTimestampUtils().toString(cal, t), oid);
>
>
>
>
> I saw that I can use PGTimestamp instead of sql Timestamp.
>
> In that case all works fine.
>
> Anyway I do not fully understand the comment and why we set oid = Oid.
> UNSPECIFIED; in case of normal sql Timestamp.
>
> Is there any different between a java.sql.Timestamp.Timestamp and
> org.postgresql.util.PGTimestamp.PGTimestamp ?
>
> Cannot be use oid = Oid.TIMESTAMP in any other case as when t is not an
> instance of PGTimestamp?
>
>
> Thanks
>
> Renzo
>
>
>
>
We use unspecified because we don't know whether setTimestamp is setting a
timestamptz or a timestamp.
Dave
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: JDBC setTimestamp question
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
2023-01-25 13:04 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-25 15:14 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-25 15:19 ` Re: JDBC setTimestamp question Dave Cramer <[email protected]>
@ 2023-01-26 09:57 ` arons <[email protected]>
2023-01-26 13:14 ` Re: JDBC setTimestamp question Dave Cramer <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: arons @ 2023-01-26 09:57 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: [email protected]
Well java Timestamp reflect the java Date which is intended to reflect
coordinated universal time (UTC) in most cases.
That is not the same in postgres with timestamp.
See also:
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29
So in generally timestamptz is what you need.
JDBC api should in my opinion reflect that nature, so a call to
setTimestamp should reflect the that nature too.
Also in the last line bindString(i, getTimestampUtils().toString(cal, t),
oid) it calls:
public synchronized String toString(@Nullable Calendar cal, Timestamp x) {
return toString(cal, x, true);
}
public synchronized String toString(@Nullable Calendar cal, Timestamp x,
boolean withTimeZone) {
Where the timezone is correctly always passed.
In the current implementation if I do not force the conversion to
timestamptz immediately we lost the timezone...not really transparent to
the java user.
See also here: https://www.postgresql.org/docs/15/datatype-datetime.html
" PostgreSQL never examines the content of a literal string before
determining its type, and therefore will treat both of the above as timestamp
without time zone. To ensure that a literal is treated as timestamp with
time zone, give it the correct explicit type: ... "
That is the most scary to me, so without an explicit cast I can maybe lost
the timezone information even if I pass on java side.
Again an explicit cast timestamptz -> timestamp would do much more sense to
me instead of avoid it, that would be clear to the user.
In addition, the currently implementation force to write specific code or
specific query based on the the back-end DB.
In our software we are supporting different DB natures and I would expect
the same behavior on different DBs for the same simple java code and query,
which is not the case here.
Maybe the code can be reconsider.
Thanks
Renzo
On Wed, Jan 25, 2023 at 4:19 PM Dave Cramer <[email protected]>
wrote:
>
>
>
> On Wed, 25 Jan 2023 at 10:15, arons <[email protected]> wrote:
>
>> I checked the source code and inside the method
>> org.postgresql.jdbc.PgPreparedStatement.setTimestamp(int, Timestamp,
>> Calendar) I found the code I was interesting to:
>>
>> int oid = Oid.UNSPECIFIED;
>>
>> // Use UNSPECIFIED as a compromise to get both TIMESTAMP and TIMESTAMPTZ
>> working.
>>
>> // This is because you get this in a +1300 timezone:
>>
>> //
>>
>> // template1=# select '2005-01-01 15:00:00 +1000'::timestamptz;
>>
>> // timestamptz
>>
>> // ------------------------
>>
>> // 2005-01-01 18:00:00+13
>>
>> // (1 row)
>>
>> // template1=# select '2005-01-01 15:00:00 +1000'::timestamp;
>>
>> // timestamp
>>
>> // ---------------------
>>
>> // 2005-01-01 15:00:00
>>
>> // (1 row)
>>
>> // template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp
>> ;
>>
>> // timestamp
>>
>> // ---------------------
>>
>> // 2005-01-01 18:00:00
>>
>> // (1 row)
>>
>> // So we want to avoid doing a timestamptz -> timestamp conversion, as
>> that
>>
>> // will first convert the timestamptz to an equivalent time in the
>> server's
>>
>> // timezone (+1300, above), then turn it into a timestamp with the
>> "wrong"
>>
>> // time compared to the string we originally provided. But going straight
>>
>> // to timestamp is OK as the input parser for timestamp just throws away
>>
>> // the timezone part entirely. Since we don't know ahead of time what
>> type
>>
>> // we're actually dealing with, UNSPECIFIED seems the lesser evil, even
>> if it
>>
>> // does give more scope for type-mismatch errors being silently hidden.
>>
>> // If a PGTimestamp is used, we can define the OID explicitly.
>>
>> if (t instanceof PGTimestamp) {
>>
>> PGTimestamp pgTimestamp = (PGTimestamp) t;
>>
>> if (pgTimestamp.getCalendar() == null) {
>>
>> oid = Oid.TIMESTAMP;
>>
>> } else {
>>
>> oid = Oid.TIMESTAMPTZ;
>>
>> cal = pgTimestamp.getCalendar();
>>
>> }
>>
>> }
>>
>> if (cal == null) {
>>
>> cal = getDefaultCalendar();
>>
>> }
>>
>> bindString(i, getTimestampUtils().toString(cal, t), oid);
>>
>>
>>
>>
>> I saw that I can use PGTimestamp instead of sql Timestamp.
>>
>> In that case all works fine.
>>
>> Anyway I do not fully understand the comment and why we set oid = Oid.
>> UNSPECIFIED; in case of normal sql Timestamp.
>>
>> Is there any different between a java.sql.Timestamp.Timestamp and
>> org.postgresql.util.PGTimestamp.PGTimestamp ?
>>
>> Cannot be use oid = Oid.TIMESTAMP in any other case as when t is not an
>> instance of PGTimestamp?
>>
>>
>> Thanks
>>
>> Renzo
>>
>>
>>
>>
> We use unspecified because we don't know whether setTimestamp is setting a
> timestamptz or a timestamp.
>
> Dave
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: JDBC setTimestamp question
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
2023-01-25 13:04 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-25 15:14 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-25 15:19 ` Re: JDBC setTimestamp question Dave Cramer <[email protected]>
2023-01-26 09:57 ` Re: JDBC setTimestamp question arons <[email protected]>
@ 2023-01-26 13:14 ` Dave Cramer <[email protected]>
2023-01-26 14:57 ` Re: JDBC setTimestamp question arons <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Dave Cramer @ 2023-01-26 13:14 UTC (permalink / raw)
To: arons <[email protected]>; +Cc: [email protected]
On Thu, 26 Jan 2023 at 04:57, arons <[email protected]> wrote:
> Well java Timestamp reflect the java Date which is intended to reflect
> coordinated universal time (UTC) in most cases.
> That is not the same in postgres with timestamp.
> See also:
> https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29
>
> So in generally timestamptz is what you need.
>
> JDBC api should in my opinion reflect that nature, so a call to
> setTimestamp should reflect the that nature too.
>
> Also in the last line bindString(i, getTimestampUtils().toString(cal, t),
> oid) it calls:
>
> public synchronized String toString(@Nullable Calendar cal, Timestamp x) {
>
> return toString(cal, x, true);
>
> }
>
> public synchronized String toString(@Nullable Calendar cal, Timestamp x,
> boolean withTimeZone) {
>
>
> Where the timezone is correctly always passed.
>
>
> In the current implementation if I do not force the conversion to
> timestamptz immediately we lost the timezone...not really transparent to
> the java user.
> See also here: https://www.postgresql.org/docs/15/datatype-datetime.html
> " PostgreSQL never examines the content of a literal string before
> determining its type, and therefore will treat both of the above as timestamp
> without time zone. To ensure that a literal is treated as timestamp with
> time zone, give it the correct explicit type: ... "
> That is the most scary to me, so without an explicit cast I can maybe lost
> the timezone information even if I pass on java side.
> Again an explicit cast timestamptz -> timestamp would do much more sense
> to me instead of avoid it, that would be clear to the user.
>
> In addition, the currently implementation force to write specific code or
> specific query based on the the back-end DB.
> In our software we are supporting different DB natures and I would expect
> the same behavior on different DBs for the same simple java code and query,
> which is not the case here.
>
> Maybe the code can be reconsider.
>
> Thanks
> Renzo
>
>
> Hi Renzo,
I feel your pain. The fact that postgres has two timestamps and java only
has one has caused more issues than any other single thing.
While applications can afford to be opinionated the driver cannot. We try
to please everyone, and in doing so sometimes please nobody.
What I mean by this is that if the user has decided they want to use
timestamp then we try to make that work. Similarly with timestamptz.
there is a PR correct mapping for postgres timestamptz type to sql type
TIMESTAMP_W… by lopata2 · Pull Request #2715 · pgjdbc/pgjdbc (github.com)
<https://github.com/pgjdbc/pgjdbc/pull/2715; which should address this
issue in the future
Regards,
Dave
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: JDBC setTimestamp question
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
2023-01-25 13:04 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-25 15:14 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-25 15:19 ` Re: JDBC setTimestamp question Dave Cramer <[email protected]>
2023-01-26 09:57 ` Re: JDBC setTimestamp question arons <[email protected]>
2023-01-26 13:14 ` Re: JDBC setTimestamp question Dave Cramer <[email protected]>
@ 2023-01-26 14:57 ` arons <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: arons @ 2023-01-26 14:57 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: [email protected]
Thanks a lot for the info.
Looking forward for that, in the mean time we will work with a patch.
BR
Renzo
On Thu, Jan 26, 2023 at 2:15 PM Dave Cramer <[email protected]>
wrote:
>
>
> On Thu, 26 Jan 2023 at 04:57, arons <[email protected]> wrote:
>
>> Well java Timestamp reflect the java Date which is intended to reflect
>> coordinated universal time (UTC) in most cases.
>> That is not the same in postgres with timestamp.
>> See also:
>> https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29
>>
>> So in generally timestamptz is what you need.
>>
>> JDBC api should in my opinion reflect that nature, so a call to
>> setTimestamp should reflect the that nature too.
>>
>> Also in the last line bindString(i, getTimestampUtils().toString(cal, t),
>> oid) it calls:
>>
>> public synchronized String toString(@Nullable Calendar cal, Timestamp x)
>> {
>>
>> return toString(cal, x, true);
>>
>> }
>>
>> public synchronized String toString(@Nullable Calendar cal, Timestamp x,
>> boolean withTimeZone) {
>>
>>
>> Where the timezone is correctly always passed.
>>
>>
>> In the current implementation if I do not force the conversion to
>> timestamptz immediately we lost the timezone...not really transparent to
>> the java user.
>> See also here: https://www.postgresql.org/docs/15/datatype-datetime.html
>> " PostgreSQL never examines the content of a literal string before
>> determining its type, and therefore will treat both of the above as timestamp
>> without time zone. To ensure that a literal is treated as timestamp with
>> time zone, give it the correct explicit type: ... "
>> That is the most scary to me, so without an explicit cast I can maybe
>> lost the timezone information even if I pass on java side.
>> Again an explicit cast timestamptz -> timestamp would do much more sense
>> to me instead of avoid it, that would be clear to the user.
>>
>> In addition, the currently implementation force to write specific code or
>> specific query based on the the back-end DB.
>> In our software we are supporting different DB natures and I would expect
>> the same behavior on different DBs for the same simple java code and query,
>> which is not the case here.
>>
>> Maybe the code can be reconsider.
>>
>> Thanks
>> Renzo
>>
>>
>> Hi Renzo,
>
> I feel your pain. The fact that postgres has two timestamps and java only
> has one has caused more issues than any other single thing.
>
> While applications can afford to be opinionated the driver cannot. We try
> to please everyone, and in doing so sometimes please nobody.
> What I mean by this is that if the user has decided they want to use
> timestamp then we try to make that work. Similarly with timestamptz.
>
> there is a PR correct mapping for postgres timestamptz type to sql type
> TIMESTAMP_W… by lopata2 · Pull Request #2715 · pgjdbc/pgjdbc (github.com)
> <https://github.com/pgjdbc/pgjdbc/pull/2715; which should address this
> issue in the future
>
> Regards,
>
> Dave
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-01-26 14:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-24 13:52 JDBC setTimestamp question arons <[email protected]>
2023-01-25 13:04 ` arons <[email protected]>
2023-01-25 15:07 ` Dave Cramer <[email protected]>
2023-01-25 15:14 ` arons <[email protected]>
2023-01-25 15:19 ` Dave Cramer <[email protected]>
2023-01-26 09:57 ` arons <[email protected]>
2023-01-26 13:14 ` Dave Cramer <[email protected]>
2023-01-26 14:57 ` arons <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox