pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
8+ messages / 4 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-06-25 10:32 "labanovichttps (@labanovichttps)" <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: labanovichttps (@labanovichttps) @ 2025-06-25 10:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**Describe the issue**
For Europe/Belgrade and "Europe/Prague" timezones, the wrong current_time formatting. They're different because of the DST(summer time +2). It happens because you set 1970-01-01(winter time +1)
**Driver Version?**
42.7.7
**Java Version?**
21
**OS Version?**
MacOS Sequoia
**PostgreSQL Version?**
**To Reproduce**
`SELECT current_time, current_timestamp;` with Europe/Belgrade or Europe/Prague timezone.
**Expected behaviour**
The current local time as same as current_timestamp is
**Logs**
If possible PostgreSQL logs surrounding the occurrence of the issue
Additionally logs from the driver can be obtained adding
Using the following template code make sure the bug can be replicated in the driver alone.
```
import java.io.IOException;
import java.sql.*;
import java.util.TimeZone;
public class Main {
public class Main {
public static void main(String[] args) throws SQLException, IOException, InterruptedException, ClassNotFoundException {
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Prague"));
String url = "jdbc:postgresql://localhost:5432/postgres";
String user = "postgres";
String pass = "pass";
Class.forName("org.postgresql.Driver");
try (Connection con = DriverManager.getConnection(url, user, pass);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select current_time, current_timestamp")) {
if (rs.next()) {
Time s = rs.getTime(1);
Timestamp ts = rs.getTimestamp(2);
System.out.println("My date " + s);
System.out.println("My timestamp " + ts);
}
}
}
}
}
```
<img width="475" alt="Image" src="https://github.com/user-attachments/assets/025603d1-eb46-4708-a047-3a901f9f3acc"; />
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-06-25 10:33 "labanovichttps (@labanovichttps)" <[email protected]>
6 siblings, 0 replies; 8+ messages in thread
From: labanovichttps (@labanovichttps) @ 2025-06-25 10:33 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Added link to our ticket
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-06-25 10:34 "labanovichttps (@labanovichttps)" <[email protected]>
6 siblings, 0 replies; 8+ messages in thread
From: labanovichttps (@labanovichttps) @ 2025-06-25 10:34 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
see org.postgresql.jdbc.PgResultSet#getTime(int, java.util.Calendar)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-06-25 11:46 "davecramer (@davecramer)" <[email protected]>
6 siblings, 0 replies; 8+ messages in thread
From: davecramer (@davecramer) @ 2025-06-25 11:46 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
interesting, thanks for the report.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-08-21 22:06 "Frank-Gu-81 (@Frank-Gu-81)" <[email protected]>
6 siblings, 0 replies; 8+ messages in thread
From: Frank-Gu-81 (@Frank-Gu-81) @ 2025-08-21 22:06 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Thanks for the discussion — following up with another observation that might help narrow this down.
## Context
I’m also seeing a possible time-of-day mismatch around DST when calling `ResultSet#getTime(int)` without a Calendar on `TIMESTAMP` / `TIMESTAMPTZ`.
## Environment
* PgJDBC: 42.7.7
* Java: 21
* PostgreSQL server: 16.4
* OS: macOS 15.6
* JVM default timezone at runtime: America/Los_Angeles
## Steps to reproduce
1. Set the JVM default zone to America/Los_Angeles (any DST-observing zone should work):
```java
TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles"));
2. Create a table and insert two rows (one in standard time, one in DST):
```sql
CREATE TEMP TABLE test_timestamps (
ts_without_tz timestamp,
ts_with_tz timestamptz
);
INSERT INTO test_timestamps VALUES
('2023-11-10 09:00:00', '2023-11-10 09:00:00-08'), -- PST (UTC-8)
('2023-03-13 09:00:00', '2023-03-13 09:00:00-07'); -- PDT (UTC-7)
```
3. Query and call `rs.getTime(col)` (no Calendar) on each column.
```java
try (var c = DriverManager.getConnection(PG_URL);
var s = c.createStatement();
var rs = s.executeQuery("select ts_without_tz, ts_with_tz from test_timestamps order by 1")) {
while (rs.next()) {
var tNoCal_noTz = rs.getTime(1); // no Calendar
var tNoCal_tz = rs.getTime(2); // no Calendar
var ts = rs.getTimestamp(2); // compare against local wall-clock at the instant
var lt = ts.toInstant().atZone(ZoneId.systemDefault()).toLocalTime();
System.out.printf("noCal timestamp=%s getTime=%s localTimeAtInstant=%s%n", ts, tNoCal_tz, lt);
}
}
```
## Observed vs expected (America/Los_Angeles)
* 2023-11-10 09:00 → `getTime(...)` returns 09:00:00 (as expected).
* 2023-03-13 09:00 → `getTime(...)` returns 08:00:00, while:
```java
var lt = ts.toInstant().atZone(ZoneId.systemDefault()).toLocalTime(); // → 09:00
```
shows 09:00 at that instant in the JVM zone.
## Possible cause
This seems consistent with the 1970-01-01 “epoch rebasing” noted earlier, and I think it's coming from the `PgResultSet#getTime(..)` path ending up in `TimestampUtils.convertToTime(...)`. Per its docs, `convertToTime()` normalizes the value so the date part becomes 1970-01-01 in the selected timezone.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-08-21 23:55 "davecramer (@davecramer)" <[email protected]>
6 siblings, 0 replies; 8+ messages in thread
From: davecramer (@davecramer) @ 2025-08-21 23:55 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
So this is an artifact of how timestamptz is handled by postgres
```
set timezone to "America/Los_Angeles";
INSERT INTO t VALUES
('2023-11-10 09:00:00', '2023-11-10 09:00:00-08'), -- PST (UTC-8)
('2023-03-13 09:00:00', '2023-03-13 09:00:00-07'); -- PDT (UTC-7);
INSERT 0 2
postgres=# table t;
ts | tz
---------------------+------------------------
2023-11-10 09:00:00 | 2023-11-10 09:00:00-08
2023-03-13 09:00:00 | 2023-03-13 09:00:00-07
(2 rows)
postgres=# set timezone to gmt;
SET
postgres=# table t;
ts | tz
---------------------+------------------------
2023-11-10 09:00:00 | 2023-11-10 17:00:00+00
2023-03-13 09:00:00 | 2023-03-13 16:00:00+00
```
Note the time that is stored in the second row is actually 1 hour earlier as. you had asked. It does not honour your UTC-7 as being the timezone for that insert it calculates the time `2023-03-13 09:00:00-07` and stores it in `America/Los_Angeles` timezone (well not really, it actually stores it in GMT)
See what the time was when I set the session timezone to GMT!
Postgres does not actually store a timezone with the timestamptz. it just takes whatever is in the column and displays it correctly for the current timezone.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-08-22 17:01 "QuChen88 (@QuChen88)" <[email protected]>
6 siblings, 0 replies; 8+ messages in thread
From: QuChen88 (@QuChen88) @ 2025-08-22 17:01 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Maybe `getTime()` is currently unable to properly handle daylight saving for time zones that have it?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"
@ 2025-08-22 18:20 "davecramer (@davecramer)" <[email protected]>
6 siblings, 0 replies; 8+ messages in thread
From: davecramer (@davecramer) @ 2025-08-22 18:20 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
See https://docs.oracle.com/javase/8/docs/api/java/sql/Time.html specifically
```
The date components should be set to the "zero epoch" value of January 1, 1970 and should not be accessed.
```
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-08-22 18:20 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-25 10:32 [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague" "labanovichttps (@labanovichttps)" <[email protected]>
2025-06-25 10:33 ` "labanovichttps (@labanovichttps)" <[email protected]>
2025-06-25 10:34 ` "labanovichttps (@labanovichttps)" <[email protected]>
2025-06-25 11:46 ` "davecramer (@davecramer)" <[email protected]>
2025-08-21 22:06 ` "Frank-Gu-81 (@Frank-Gu-81)" <[email protected]>
2025-08-21 23:55 ` "davecramer (@davecramer)" <[email protected]>
2025-08-22 17:01 ` "QuChen88 (@QuChen88)" <[email protected]>
2025-08-22 18:20 ` "davecramer (@davecramer)" <[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