Subject: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database

From: "anaconda875 (@anaconda875)" <[email-masked]>
Date: Thu, 07 Nov 2024 15:15:39 +0000

**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

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 07 Nov 2024 15:26:19 +0000

Can you simplify the example?
Why do you use both `OffsetDateTime`, `Date`, and `Timestamp`? What is the expected behaviour?

Frankly speaking, I am leaning towards "works as expected" as you use `timestamp` data type in the database, so it can't really store "instant".

If you want storing instants, use `timestamp with time zone`.

---

From: "anaconda875 (@anaconda875)" <[email-masked]>
Date: Thu, 07 Nov 2024 16:21:53 +0000

`Date` is not persisted, it is just a way to create a `Timestamp`. I know having both `Date`  àd `OffsetDateTime` is weird. Actualy, i have 2 tables, one is having Date, one have OffsetDateTime.
The issue is, both Java datetime have the same value, but persisted differently to the DB.
I don't really care about the `timestamp with time zone`, i just call 2 `set***` methods` of jdbc and i would expect it work correctly

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 07 Nov 2024 16:24:32 +0000

They both work correctly

---

From: "anaconda875 (@anaconda875)" <[email-masked]>
Date: Thu, 07 Nov 2024 16:25:24 +0000

Then why 2 datetime with the same value, same timezone gmt-2 are different in the db?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 07 Nov 2024 19:30:31 +0000

> Then why 2 datetime with the same value, same timezone gmt-2 are different in the db?

Could you please settle on a single value, and provide expected and the actual output?
As you provide two different approaches to set values, it is not clear if they should behave the same.

---

From: "anaconda875 (@anaconda875)" <[email-masked]>
Date: Fri, 08 Nov 2024 01:42:51 +0000

1. For Date:
Given a Date `2024-11-07 15:08:57.379` with TimeZone of `GMT-2`:
`preparedStatement.setTimestamp( 1, timestamp, Calendar.getInstance( TimeZone.getTimeZone( "GMT-2" ) ) );`
Expected: When `executeUpdate` should persist `2024-11-07 13:08:57.379`
Actual: as Expected
2. For OffsetDateTime:
Given an OffsetDatime `2024-11-07 15:08:57.379` with TimeZone of `GMT-2`:
`preparedStatement.setObject( 2, offsetDateTime
								  .atZoneSameInstant( TimeZone.getTimeZone( "GMT-2" ).toZoneId() )
								  .toOffsetDateTime(),
						  SqlTypes.TIMESTAMP_WITH_TIMEZONE );`
Expected: When `executeUpdate` should persist `2024-11-07 13:08:57.379` (same as 1)
Actual: `2024-11-07 15:08:57.379`

---

From: "anaconda875 (@anaconda875)" <[email-masked]>
Date: Fri, 08 Nov 2024 06:13:35 +0000

---

From: "anaconda875 (@anaconda875)" <[email-masked]>
Date: Sat, 09 Nov 2024 04:47:07 +0000

@vlsi any update?

---

From: "anaconda875 (@anaconda875)" <[email-masked]>
Date: Fri, 07 Feb 2025 16:14:52 +0000

Hi @vlsi , any update?