pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
10+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-07 15:15 "anaconda875 (@anaconda875)" <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: anaconda875 (@anaconda875) @ 2024-11-07 15:15 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-07 15:26 "vlsi (@vlsi)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: vlsi (@vlsi) @ 2024-11-07 15:26 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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`.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-07 16:21 "anaconda875 (@anaconda875)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: anaconda875 (@anaconda875) @ 2024-11-07 16:21 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
`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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-07 16:24 "vlsi (@vlsi)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: vlsi (@vlsi) @ 2024-11-07 16:24 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
They both work correctly
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-07 16:25 "anaconda875 (@anaconda875)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: anaconda875 (@anaconda875) @ 2024-11-07 16:25 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Then why 2 datetime with the same value, same timezone gmt-2 are different in the db?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-07 19:30 "vlsi (@vlsi)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: vlsi (@vlsi) @ 2024-11-07 19:30 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-08 01:42 "anaconda875 (@anaconda875)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: anaconda875 (@anaconda875) @ 2024-11-08 01:42 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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`
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-08 06:13 "anaconda875 (@anaconda875)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: anaconda875 (@anaconda875) @ 2024-11-08 06:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Simply put, 2 params have the same datetime and the same timezone `GMT-2`
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2024-11-09 04:47 "anaconda875 (@anaconda875)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: anaconda875 (@anaconda875) @ 2024-11-09 04:47 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi any update?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database
@ 2025-02-07 16:14 "anaconda875 (@anaconda875)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: anaconda875 (@anaconda875) @ 2025-02-07 16:14 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Hi @vlsi , any update?
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-02-07 16:14 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-07 15:15 [pgjdbc/pgjdbc] issue #3433: Same timestamps, but different values when saved (executeUpdate) to database "anaconda875 (@anaconda875)" <[email protected]>
2024-11-07 15:26 ` "vlsi (@vlsi)" <[email protected]>
2024-11-07 16:21 ` "anaconda875 (@anaconda875)" <[email protected]>
2024-11-07 16:24 ` "vlsi (@vlsi)" <[email protected]>
2024-11-07 16:25 ` "anaconda875 (@anaconda875)" <[email protected]>
2024-11-07 19:30 ` "vlsi (@vlsi)" <[email protected]>
2024-11-08 01:42 ` "anaconda875 (@anaconda875)" <[email protected]>
2024-11-08 06:13 ` "anaconda875 (@anaconda875)" <[email protected]>
2024-11-09 04:47 ` "anaconda875 (@anaconda875)" <[email protected]>
2025-02-07 16:14 ` "anaconda875 (@anaconda875)" <[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