[pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support

pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  
help / color / mirror / Atom feed

[pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
25+ messages / 7 participants
[nested] [flat]

* [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-10-29 18:46 "bokken (@bokken)" <[email protected]>
  0 siblings, 0 replies; 25+ messages in thread

From: bokken (@bokken) @ 2018-10-29 18:46 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

**I'm submitting a ...**

  <!--- What kind of an issue is this? Put an `x` in all the boxes that apply: -->
- [ ] bug report
- [x ] feature request

**Describe the issue**
Additional support for jsr 310 data types

**Java Version**
1.8

**OS Version**
N/A

**PostgreSQL Version**
All

**To Reproduce**
N/A

**Expected behaviour**
ResultSet.getObject(Class) should have following behaviors:
For TIMESTAMP and TIMESTAMPTZ:

Instant should be supported for TIMESTAMP and TIMESTAMPTZ

ZonedDateTime
  For TIMESTAMP, the local default ZoneID should be used.
  For TIMESTAMPTZ, UTC should be used

OffsetDateTime, LocalDateTime, LocalDate and LocalTime call all be supported by calling the relevant to* methods on ZonedDateTime.

For DATE:

java.sql.Date and LocalDate should be supported (existing behavior)

For TIME
java.sql.Time and LocalTime should be supported (existing behavior)

PreparedStatement.setObject(Object, int) should have the following behaviors:
For Type TIMESTAMP and TIMESTAMP_WITH_TIMEZONE objects of following type should be supported:
Instant, OffsetDateTime, ZonedDateTime, LocalDateTime.

PreparedStatement.setObject(Object) should add the following objects:
Instant (same as a Timestamp)
ZonedDateTime same as OffsetDateTime (using toOffsetDateTime conversion).

**Logs**
N/A

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-09 21:05 ` "trtrmitya (@trtrmitya)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: trtrmitya (@trtrmitya) @ 2018-11-09 21:05 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

The lack of `java.time.Instant` support is really a pain :(

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-14 22:44 ` "marschall (@marschall)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: marschall (@marschall) @ 2018-11-14 22:44 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

These are my personal views:
* Supporting Instant for TIMESTAMPTZ is fine. It is not covered by the JDBC 4.2 specification but it is semantically equivalent. I find it not particularly useful as I find Instant a not particularly useful datatype, it's just an int and a long.
* I am against supporting Instant for TIMESTAMP. These are semantically different data types. When converting between Instant and TIMESTAMP you have two options and both seem wrong
  * use the system default time zone which means you'll get different values/instants depending on the system default time zone. Additionally in case of DST transitions you can get reorderings and the duration between instants can change .
  * use UTC which adds semantics to TIMESTAMP which it doesn't have and will possibly users as the value stored on the database is different from the local time
* Supporting ZonedDateTime for TIMESTAMPTZ is fine. It is not covered by the JDBC 4.2 specification but it is semantically equivalent. I would also use UTC as that's what Postgres is storing. There is potential here as well for user confusion as they will likely get a different offset back. However they will get the same instant and that's consistent with the current behavior of OffsetDateTime.
* I am against supporting ZonedDateTime for TIMESTAMP. These are semantically different data types. If you're using the default zone id you will get different instants depending on the default zone id. Also in the case of DST transitions you can get reorderings and some of the conversions are ambiguous as there are two possible offsets to chose from.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-15 13:40 ` "bokken (@bokken)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: bokken (@bokken) @ 2018-11-15 13:40 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

@marschall, I completely agree that the postgresql TIMESTAMP type has complicated mappings into java data types. I guess in light of the inconsistencies I prefer to take the approach of providing similar/unsurprising behavior between the jsr310 types and the legacy types (specifically java.sql.Timestamp, java.util.Date, and Calendar).

`Instant` and `java.sql.Timestamp` (and `java.util.Date`) are semantically identical data types. They all represent an exact point in time (to varying degrees of precision), with no regard to time zone. If we support `getObject(int, java.sql.Timestamp,class)` and `getObject(int, java.util.Date.class)` for a TIMESTAMP column, it would seem to be consistent to also support `Instant`.

Similarly a `ZonedDateTime` is semantically equivalent to a (populated) `Calendar`. We currently support `getObject(int, Calendar.class)` for TIMESTAMP.

While there are certainly drawbacks to the TIMESTAMP data type, my inclination would be to provide as broad support as possible. However, if stricter behavior is desired, I can certainly make those changes. Is this an accurate representation of what you would like to supported?

TIMESTAMPTZ

- Instant
- OffsetDateTime (+0000)
- ZonedDateTime (UTC)
- LocalDateTime
- Timestamp
- java.util.Date
- java.util.Calendar (system default)

TIMESTAMP

- ~~Instant~~
- OffsetDateTime (**system default**)
- ~~ZonedDateTime (UTC)~~
- LocalDateTime
- Timestamp
- java.util.Date
- java.util.Calendar (system default)

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-15 13:55 ` "marschall (@marschall)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: marschall (@marschall) @ 2018-11-15 13:55 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

@bokken 

> Instant and java.sql.Timestamp (and java.util.Date) are semantically identical data types. They all represent an exact point in time (to varying degrees of precision), with no regard to time zone.

This is unfortunately not sure. java.sql.Timestamp is bound to the JVM default time zone. Let's say you have `2018-11-15T14:48` on the database and your JVM default time zone is +1 you will get a Timestamp and instant for `2018-11-15T14:48+01:00`. If your JVM default time zone is +2 you get a Timestamp and instant for `2018-11-15T14:48+02:00` which is different.

I feel allowing these kinds of conversions will further perpetuate the subtle time zone bugs that people are complaining about with the existing java.sql and java.util.Date APIs.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-15 14:06 ` "bokken (@bokken)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: bokken (@bokken) @ 2018-11-15 14:06 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

`Timestamp` as a data type is not bound the the jvm time zone. The jdbc api for setTimestamp and getTimestamp have specified the use of the default timezone when no Calendar is provided. The `toString` implementation of `java.util.Date` (and thus java.sql.Timestamp) use the timezone of the jvm when constructing a String.

The example you give show both java.sql.Timestamp and java.time.Instant having the same behavior, which was my point of them being the same data type.

> I feel allowing these kinds of conversions will further perpetuate the subtle time zone bugs that people are complaining about with the existing java.sql and java.util.Date APIs.

I certainly understand that there are challenges with the TIMESTAMP datatype and that users should be encouraged to use TIMESTAMPTZ. But should this project "force" that by limiting the support for TIMESTAMP with the introduction of new data types?

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-16 17:30 ` "marschall (@marschall)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: marschall (@marschall) @ 2018-11-16 17:30 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

> Timestamp as a data type is not bound the the jvm time zone.

Unfortunately it is. Consider the following code:

```java
LocalDateTime localDateTime = LocalDateTime.of(2018, 11, 16, 18, 2);

TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles"));
Instant instant1 = Timestamp.valueOf(localDateTime).toInstant();
long epochMilli1 = instant1.toEpochMilli();

TimeZone.setDefault(TimeZone.getTimeZone("America/New_York"));
Instant instant2 = Timestamp.valueOf(localDateTime).toInstant();
long epochMilli2 = instant2.toEpochMilli();

System.out.printf("epochMilli1: %d%n", epochMilli1);
System.out.printf("epochMilli2: %d%n", epochMilli2);
System.out.println("same instant: " + instant1.equals(instant2));
```

Which prints

```
epochMilli1: 1542420120000
epochMilli2: 1542409320000
same instant: false
```

As you can see the time java.sql.Timestamp -> java.time.Instant conversion depends on the JVM default time zone.

This becomes quite dramatic when a user does something like this on a TIMESTAMP column:

```java
OffsetDateTime.ofInstant(resultSet.getObject(1, Instant.class), ZoneOffset.UTC);
```

> I certainly understand that there are challenges with the TIMESTAMP datatype and that users should be encouraged to use TIMESTAMPTZ. But should this project "force" that by limiting the support for TIMESTAMP with the introduction of new data types?

Ultimately the project needs to decide. You'll have to explain to users anyway that in order for what they want to work correctly they will have to switch data types. You can only chose if the trigger is either a reproducible exception that always happens and informs them about incompatible data types or a rare and hard to reproduce bug in production that may have already cased data corruption.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-16 18:12 ` "bokken (@bokken)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: bokken (@bokken) @ 2018-11-16 18:12 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

> As you can see the time java.sql.Timestamp -> java.time.Instant conversion depends on the JVM default time zone.

I am not sure I agree. What I see is that `LocalDateTime` -> `Timestamp` conversion depends on the JVM default time zone. Which is what the javadoc of the valueOf method in Timestamp[1] indicates:

> The provided LocalDateTime is interpreted as the local date-time in the local time zone.

> Ultimately the project needs to decide. You'll have to explain to users anyway that in order for what they want to work correctly they will have to switch data types. You can only chose if the trigger is either a reproducible exception that always happens and informs them about incompatible data types or a rare and hard to reproduce bug in production that may have already cased data corruption.

I completely agree that the project needs to decide. I somewhat disagree on comparisons of trigger, but I do understand your point.

[1] - https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html#valueOf-java.time.LocalDateTime-

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2018-11-16 21:56 ` "marschall (@marschall)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: marschall (@marschall) @ 2018-11-16 21:56 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

> I am not sure I agree. What I see is that LocalDateTime -> Timestamp conversion depends on the JVM default time zone.

It's the same with String -> Timestamp conversion which like LocalDateTime calls the deprecated constructor, see code below. To get from a date time / SQL TIMESTAMP to an instant / point in time / unix timestamp you need to have a time zone. For java.sql.Timestamp this is the JVM default time zone.

```java
String localDateTime = "2018-11-16 18:02:00";

TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles"));
Instant instant1 = Timestamp.valueOf(localDateTime).toInstant();
long epochMilli1 = instant1.toEpochMilli();

TimeZone.setDefault(TimeZone.getTimeZone("America/New_York"));
Instant instant2 = Timestamp.valueOf(localDateTime).toInstant();
long epochMilli2 = instant2.toEpochMilli();

System.out.printf("epochMilli1: %d%n", epochMilli1);
System.out.printf("epochMilli2: %d%n", epochMilli2);
System.out.println("same instant: " + instant1.equals(instant2));
```

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2023-03-14 10:03 ` "vlsi (@vlsi)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: vlsi (@vlsi) @ 2023-03-14 10:03 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

The issue with `ZonedDateTime` is that its `ZoneId` might have several variations:
* `ZoneOffset`. In that case, it is pretty much the same thing as `OffsetDateTime`, so we can support it
* `ZoneRegion`. In that case, the zone can have rules regarding DST, however, the database can't really store the zone name, and it applies the current rules. However, it accepts the time zone name, so the DB does not reject timestamps with named zones.

It looks like we might implement `ZonedDateTime` as `ZonedDateTime.toOffsetDateTime()`, so we convert named zones to offsets.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2023-10-30 15:17 ` "guizmaii (@guizmaii)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: guizmaii (@guizmaii) @ 2023-10-30 15:17 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

Hi everyone,

Is there any plan to get official support for `java.time.Instant`?

Based, on the discussion above, it seems that there's a consensus over mapping it to a `TIMESTAMP WITH TIMEZONE`.

Re-using the `java.time.OffsetDateTime` encoder/code (I don't know the internal of this lib) seems to be appropriate, passing it the `Instant` value by converting it to an `OffsetDateTime` with `OffsetDateTime.ofInstant(instantInstance, ZoneOffset.UTC)`, no?

Is there anything preventing/blocking such support from being integrated into this driver?

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2023-10-30 15:34 ` "davecramer (@davecramer)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: davecramer (@davecramer) @ 2023-10-30 15:34 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

well there was a PR which would fix this https://github.com/pgjdbc/pgjdbc/pull/2835 however we requested changes and the original author did not provide them. Care to fix up the PR ?

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2023-10-31 07:38 ` "guizmaii (@guizmaii)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: guizmaii (@guizmaii) @ 2023-10-31 07:38 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

@davecramer This PR seems to be a bit different from what I'm talking about. It seems to be modifying the default mapping between `java.sql.Timestamp` and the DB, moving it from `TIMESTAMP` to `TIMESTAMPZ` if some configuration is enabled.
I'm talking about bringing official support to `java.time.Instant` in the same way as `java.time.OffsetDateTime` is officially supported, see https://jdbc.postgresql.org/documentation/query/#using-java-8-date-and-time-classes

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2023-10-31 20:40 ` "davecramer (@davecramer)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: davecramer (@davecramer) @ 2023-10-31 20:40 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

@bokken do you agree that it should be mapped to timetamptz ?

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2024-08-07 21:58 ` "michalkoza (@michalkoza)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: michalkoza (@michalkoza) @ 2024-08-07 21:58 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

@marschall Conversion from java.sql.Timestamp -> Instant is independent of time zone. Check this code:
```java
TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles"));
LocalDateTime localDateTime = LocalDateTime.of(2018, 11, 16, 18, 2);
Timestamp timestamp = Timestamp.valueOf(localDateTime);
Instant instant1 = timestamp.toInstant();
System.out.println("timestamp in LA zone " + timestamp);
System.out.println("instant1  in LA zone " + instant1);

TimeZone.setDefault(TimeZone.getTimeZone("America/New_York"));
Instant instant2 = timestamp.toInstant();
System.out.println("timestamp in NY zone " + timestamp);
System.out.println("instant1  in NY zone " + instant1);
System.out.println("instant2  in NY zone " + instant2);
long epochMilli1 = instant1.toEpochMilli();
long epochMilli2 = instant2.toEpochMilli();
System.out.printf("epochMilli1: %d%n", epochMilli1);
System.out.printf("epochMilli2: %d%n", epochMilli2);
System.out.println("same instant: " + instant1.equals(instant2));
```

If you have a fixed timestamp, you can change time zones and convert it to Instant many times, always getting the same result. Observe also that even though the `timestamp` is never changed it is displayed differently depending on time zone, but it keeps the same value.

In your example the difference comes from the fact that 18:02 in LA is different moment in time than 18:02 in NY. It would be surprising if your Instants and Timestamps would be equal.

`Instant` is a moment in time in UTC. The same goes for `TIMESTAMP` type in PostgreSQL. They are both timezone-agnostic. So I struggle to understand why would you not want to store Instant as TIMESTAMP rather than TIMESTAMPTZ

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 04:11 ` "vlsi (@vlsi)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: vlsi (@vlsi) @ 2025-07-28 04:11 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

There are multiple demands mixed in the issue, so I decided to give it a try and implemented `Instant` for `timestamptz` and `timetz`.

See #3734

> marschall: I am against supporting Instant for TIMESTAMP

+1

---

Regarding `ZonedDateTime`. I believe we should support them as well.
The extra motivation is that PostgreSQL happily supports `'America/New_York'` in `timestamptz` literals, and it just converts the rich timezone name to a UTC.
If we do the same thing. For example if take `ZonedDateTime with rich ZoneRegion` and convert it to `OffsetDateTime` when sending the value to the DB, it is not much different from the case user went with `TIMESTAMP '... America/New_York'` literal.

---

> michalkoza: Instant is a moment in time in UTC. The same goes for TIMESTAMP type in PostgreSQL

@michalkoza , please check out https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

> `timestamp` (also known as `timestamp without time zone`) doesn't do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - **you don't know what time it records**. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter **may give the wrong answer**.

> So if what you want to store is a point in time, rather than a picture of a clock, use `timestamptz`.

On top of that, there's an explicit `Don't use timestamp (without time zone) to store UTC times` section.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 05:21 ` "vlsi (@vlsi)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: vlsi (@vlsi) @ 2025-07-28 05:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

A caveat with `ZonedDateTime` is it has neither `MIN` nor `MAX` constants, so it is not clear how to map from `+-infinity` to `ZonedDateTime`. The reason for missing `MIN/MAX` is it is unclear which timezone would have MIN/MAX value.

I think it might be safe to throw an error if user attempts accessing `infinity` values with `ZonedDateTime`.

@bokken , @marschall , @davecramer , WDYT about `infinity` for `ZonedDateTime`?

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 09:27 ` "michalkoza (@michalkoza)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: michalkoza (@michalkoza) @ 2025-07-28 09:27 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

@vlsi Thanks for reading my input! 
I should not have said: 
> michalkoza: Instant is a moment in time in UTC.

It is not accurate - it is just default `.toString()` behaviour on this type.

This part of the article represents my point of view: https://wiki.postgresql.org/wiki/Don%27t_Do_This#When_should_you.3F_8

I think that by choosing `Instant` in the app, and `TIMESTAMP` in DB - I'm clearly saying:
"Dear database, I handle time zones on the app side. You do not have to worry about it at all"
Which I think is a super legit approach :) It is hard for me to imagine scenario in which it would be confusing or ambiguous. 

... But I have to admit that learning this:
> timestamptz records a single moment in time. Despite what the name says it doesn't store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC

does convince me that `Instant` -> `TIMESTAMPZ` is NOT a bad idea :)

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 10:01 ` "vlsi (@vlsi)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: vlsi (@vlsi) @ 2025-07-28 10:01 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

> "Dear database, I handle time zones on the app side. You do not have to worry about it at all"
>Which I think is a super legit approach :) 

It defeats ad-hoc SQL usage though.
In other words, even though the application might try its best to use "safely" such "instants", a user who executes an SQL (reporting, data fix, etc) might fail to deal with such `timestamp` properly. Previously `Instant.class` access failed, so no application could rely on `timestamp -> Instant` conversion. That is why I would like to keep the door closed so the error surfaces at the development time, and users replace `timestamp` to `timestamptz` sooner rather than later if they want accessing it as `Instant`.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 10:19 ` "davecramer (@davecramer)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: davecramer (@davecramer) @ 2025-07-28 10:19 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

> > "Dear database, I handle time zones on the app side. You do not have to worry about it at all"
> > Which I think is a super legit approach :)
> 
> It defeats ad-hoc SQL usage though. In other words, even though the application might try its best to use "safely" such "instants", a user who executes an SQL (reporting, data fix, etc) might fail to deal with such `timestamp` properly. Previously `Instant.class` access failed, so no application could rely on `timestamp -> Instant` conversion. That is why I would like to keep the door closed so the error surfaces at the development time, and users replace `timestamp` to `timestamptz` sooner rather than later if they want accessing it as `Instant`.

I think for a very limited number of people this is a legitimate approach. I'm not sure how to provide correct implementation for most people and this approach in the same driver.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 10:21 ` "davecramer (@davecramer)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: davecramer (@davecramer) @ 2025-07-28 10:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

> A caveat with `ZonedDateTime` is it has neither `MIN` nor `MAX` constants, so it is not clear how to map from `+-infinity` to `ZonedDateTime`. The reason for missing `MIN/MAX` is it is unclear which timezone would have MIN/MAX value.
> 
> I think it might be safe to throw an error if user attempts accessing `infinity` values with `ZonedDateTime`.
> 
> [@bokken](https://github.com/bokken) , [@marschall](https://github.com/marschall) , [@davecramer](https://github.com/davecramer) , WDYT about `infinity` for `ZonedDateTime`?

Seems like the spec is a lacking here. According to the docs ZonedDateTime is supposed to be OffsetDateTime with zone rules but it seems like the ZonedDateTime missed having MIN/MAX. 

 

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 10:52 ` "vlsi (@vlsi)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: vlsi (@vlsi) @ 2025-07-28 10:52 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

I've added `ZonedDateTime` support as well with errors on `infinity`/`-infinity`

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 11:59 ` "bokken (@bokken)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: bokken (@bokken) @ 2025-07-28 11:59 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

Is providing support for (getting) OffsetDateTime and ZonedDateTime by always interpreting as UTC really valuable?
I understand the desire to be liberal in datatype mappings supported, but the lack of actually storing offset and/or time zone make this type lossy.

I think throwing an exception for +/- infinity timestamps is fine.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 12:11 ` "vlsi (@vlsi)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: vlsi (@vlsi) @ 2025-07-28 12:11 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

>Is providing support for (getting) OffsetDateTime and ZonedDateTime by always interpreting as UTC really valuable?

1) `OffsetDateTime` has been implemented in https://github.com/pgjdbc/pgjdbc/pull/2467, and I believe it is way better that all the `java.util.Date`, `java.sql.Timestamp`, `java.util.Calendar` alternatives. Sure PostgreSQL loses user-provided zone, however, we can't overcome it.

2) Both `OffsetDateTime`, `ZonedDateTime`, and `Instant` represent a moment-in-time, which maps nicely to `timestamptz`. It is quite useful for working with `timestamptz`.

  Of course, it would be nice if PostgreSQL could store timezone as a text-like region name, however, that feature is not available in the backend yet. If they ever improve `timestamptz` we could revise our `ZonedDateTime` mapping.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support
@ 2025-07-28 12:55 ` "bokken (@bokken)" <[email protected]>
  23 siblings, 0 replies; 25+ messages in thread

From: bokken (@bokken) @ 2025-07-28 12:55 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

I think that linked issue was adding `OffsetTime` - and `OffsetDateTime` even predates it - but the overall point that it has been around for a long time remains.

This is shaking loose some memories. The jdbc 4.2 spec explicitly called out `OffsetDateTime` - and the work done to support jdbc 4.2 only covered the datatypes explicitly listed.

`timestamptz` maps very nicely to `Instant` and `java.sql.Timestamp`/`java.util.Date` - in that all are an point in time. `OffsetDateTime` and `ZonedDateTime` add the context of specific offset and full time zone respectively. Because of jdbc 4.2, we pretty much have to support `OffsetDateTime` - we can extend that to `ZonedDateTime`, but since the database does not support either timezone or offset data, there could be value in not supporting additional types not required by the spec which imply more functionality than the database can provide. There is already repeated confusion about what `timestamptz` actually stores.

If we are going to do `ZonedDateTime`, there might be value in making the ZoneId exactly `UTC` rather than just inheriting from `OffsetDateTime` and offset of 0.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

end of thread, other threads:[~2025-07-28 12:55 UTC | newest]

Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-29 18:46 [pgjdbc/pgjdbc] issue #1325: Additional jsr 310 support "bokken (@bokken)" <[email protected]>
2018-11-09 21:05 ` "trtrmitya (@trtrmitya)" <[email protected]>
2018-11-14 22:44 ` "marschall (@marschall)" <[email protected]>
2018-11-15 13:40 ` "bokken (@bokken)" <[email protected]>
2018-11-15 13:55 ` "marschall (@marschall)" <[email protected]>
2018-11-15 14:06 ` "bokken (@bokken)" <[email protected]>
2018-11-16 17:30 ` "marschall (@marschall)" <[email protected]>
2018-11-16 18:12 ` "bokken (@bokken)" <[email protected]>
2018-11-16 21:56 ` "marschall (@marschall)" <[email protected]>
2023-03-14 10:03 ` "vlsi (@vlsi)" <[email protected]>
2023-10-30 15:17 ` "guizmaii (@guizmaii)" <[email protected]>
2023-10-30 15:34 ` "davecramer (@davecramer)" <[email protected]>
2023-10-31 07:38 ` "guizmaii (@guizmaii)" <[email protected]>
2023-10-31 20:40 ` "davecramer (@davecramer)" <[email protected]>
2024-08-07 21:58 ` "michalkoza (@michalkoza)" <[email protected]>
2025-07-28 04:11 ` "vlsi (@vlsi)" <[email protected]>
2025-07-28 05:21 ` "vlsi (@vlsi)" <[email protected]>
2025-07-28 09:27 ` "michalkoza (@michalkoza)" <[email protected]>
2025-07-28 10:01 ` "vlsi (@vlsi)" <[email protected]>
2025-07-28 10:19 ` "davecramer (@davecramer)" <[email protected]>
2025-07-28 10:21 ` "davecramer (@davecramer)" <[email protected]>
2025-07-28 10:52 ` "vlsi (@vlsi)" <[email protected]>
2025-07-28 11:59 ` "bokken (@bokken)" <[email protected]>
2025-07-28 12:11 ` "vlsi (@vlsi)" <[email protected]>
2025-07-28 12:55 ` "bokken (@bokken)" <[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