Subject: [pgjdbc/pgjdbc] issue #3692: Wrong time formatting ResultSet.getTime(); for Europe/Belgrade and "Europe/Prague"

From: "labanovichttps (@labanovichttps)" <[email-masked]>
Date: Wed, 25 Jun 2025 10:32:07 +0000

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

---

From: "labanovichttps (@labanovichttps)" <[email-masked]>
Date: Wed, 25 Jun 2025 10:33:26 +0000

Added link to our ticket

---

From: "labanovichttps (@labanovichttps)" <[email-masked]>
Date: Wed, 25 Jun 2025 10:34:54 +0000

see org.postgresql.jdbc.PgResultSet#getTime(int, java.util.Calendar)

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 25 Jun 2025 11:46:31 +0000

interesting, thanks for the report.

---

From: "Frank-Gu-81 (@Frank-Gu-81)" <[email-masked]>
Date: Thu, 21 Aug 2025 22:06:38 +0000

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.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 21 Aug 2025 23:55:52 +0000

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.

---

From: "QuChen88 (@QuChen88)" <[email-masked]>
Date: Fri, 22 Aug 2025 17:01:44 +0000

Maybe `getTime()` is currently unable to properly handle daylight saving for time zones that have it?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 22 Aug 2025 18:20:46 +0000

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.
```