pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: anaconda875 (@anaconda875) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
Date: Thu, 07 Nov 2024 15:15:39 +0000
Message-ID: <[email protected]> (raw)
**Describe the issue**
I try to insert a row to pgdb:
```sql
create table model (id bigint not null, date1 timestamp(6), date2 timestamp(6), primary key (id))
```
I create 2 Java timestamps:
```java
Date date = new Date();
Timestamp timestamp = new Timestamp( date.getTime() );
OffsetDateTime offsetDateTime = OffsetDateTime.ofInstant( Instant.ofEpochMilli( date.getTime() ), ZoneId.systemDefault() );
//BOTH timestamp and offsetDateTime HAVING THE SAME VALUE
System.out.println(timestamp); //THIS PRINT 2024-11-07 15:08:57.379
System.out.println(offsetDateTime); //THIS PRINT 2024-11-07T15:08:57.379Z
```
Then bind these two params:
```java
preparedStatement.setTimestamp( 1, timestamp, Calendar.getInstance( TimeZone.getTimeZone( "GMT-2" ) ) ); //AT GMT-2 TZ
preparedStatement.setObject( 2, offsetDateTime
.atZoneSameInstant( TimeZone.getTimeZone( "GMT-2" ).toZoneId() ) //AT GMT-2 TZ
.toOffsetDateTime(),
SqlTypes.TIMESTAMP_WITH_TIMEZONE );
```
Simply put, 2 params have the same datetime and the same timezone `GMT-2`
Moreover, debugged into `PgPreparedStatement.executeWithFlags` and see that `preparedParameters.paramValues` have the same String **2024-11-07 13:08:57.379-02**, BUT `preparedParameters.paramTypes` HAVE DIFFERENT VALUE:
I believe the `preparedParameters.paramTypes` are from the oid(s).
So that is why I got this in DB:
```
id | date1 | date2
----+-------------------------+-------------------------
1 | 2024-11-07 13:08:57.379 | 2024-11-07 15:08:57.379
```
**2hrs difference.**
**Driver Version?**
42.7.1 (may be lower still be affected)
**Java Version?**
17
**OS Version?**
Windows 11
**PostgreSQL Version?**
15
**To Reproduce**
```java
/*create table model (id bigint not null, date1 timestamp(6), date2 timestamp(6), primary key (id)) */
public class Main {
public static void main(String[] args) throws Exception {
System.setProperty("user.timezone", "UTC");
try (Connection connection = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/hibernate_orm_test",
"user",
"pass"
)) {
PreparedStatement preparedStatement = connection.prepareStatement( "insert into model values(1, ?, ?)" );
Date date = new Date();
Timestamp timestamp = new Timestamp( date.getTime() );
OffsetDateTime offsetDateTime = OffsetDateTime.ofInstant( Instant.ofEpochMilli( date.getTime() ), ZoneId.systemDefault() );
preparedStatement.setTimestamp( 1, timestamp, Calendar.getInstance( TimeZone.getTimeZone( "GMT-2" ) ) );
preparedStatement.setObject( 2, offsetDateTime
.atZoneSameInstant( TimeZone.getTimeZone( "GMT-2" ).toZoneId() )
.toOffsetDateTime(),
SqlTypes.TIMESTAMP_WITH_TIMEZONE );
System.out.println(timestamp); //THIS PRINT 2024-11-07 15:08:57.379
System.out.println(offsetDateTime); //THIS PRINT 2024-11-07T15:08:57.379Z
System.out.println(preparedStatement.executeUpdate());
}
}
```
**Expected behaviour**
2 datetime with the **same value and timezone** should have the same value when persisting to the db.
**Logs**
No log
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: github://pgjdbc/pgjdbc
Cc: [email protected], [email protected]
Subject: Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
In-Reply-To: <<[email protected]>>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox