Message-ID: <gh-pgjdbc-pgjdbc-3433@github.com>
From: "anaconda875 (@anaconda875)" <noreply+anaconda875@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Thu, 07 Nov 2024 15:15:39 +0000
Subject: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 10293078
X-GitHub-Author-Login: anaconda875
X-GitHub-Issue: 3433
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3433
Content-Type: text/plain; charset=utf-8

**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:
![image](https://github.com/user-attachments/assets/19dbdc0c-2adb-46cd-b8e7-daeeecc84afe)

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