Driver throws java.time.format.DateTimeParseException

[kwesterfeld2]

Our application is doing a "setString()" on a PreparedStatement, whose parameter is a SQL interval. In prior versions of the driver, this string would be passed on to PG, but recent versions now try to convert the value to a java.time entity. The problem is, we are passing a legal interval value into setString(), which PG can parse, but the java.time.Period cannot. The simplest of these is simply passing "1 day".

Sample SQL:

  select * 
  from foo
  where (current_timestamp - start_time) < '1 day'

In this case "1 day" is a parameter ?

java.time.format.DateTimeParseException: Text cannot be parsed to a Period
        at java.time.Period.parse(Period.java:350)[:1.8.0_252]
        at com.impossibl.postgres.api.data.Interval.parse(Interval.java:336)
        at com.impossibl.postgres.system.procs.AutoConvertingEncoder$FromStringConverter.convert(BaseEncoders.java:93)
        at com.impossibl.postgres.system.procs.AutoConvertingBinaryEncoder.convertInput(BaseEncoders.java:126)
        at com.impossibl.postgres.system.procs.AutoConvertingBinaryEncoder.encodeValue(BaseEncoders.java:141)
        at com.impossibl.postgres.system.procs.BaseBinaryEncoder.encode(BaseEncoders.java:59)
        at com.impossibl.postgres.system.procs.BaseBinaryEncoder.encode(BaseEncoders.java:41)
        at com.impossibl.postgres.jdbc.PGPreparedStatement.set(PGPreparedStatement.java:193)
        at com.impossibl.postgres.jdbc.PGPreparedStatement.set(PGPreparedStatement.java:163)
        at com.impossibl.postgres.jdbc.PGPreparedStatement.setString(PGPreparedStatement.java:669)

[kdubb]

@kwesterfeld2 The decision was made to use "Java" time classes (which are usable in Java) in the driver and that is what you are seeing here. This change happened at the beginning of the 0.8.x series.

I think the only real solution (without rewriting driver internals) is to cast the value in the SQL query; using something like the_interval::interval::varchar. This would ensure the driver treats the value as text while the server will convert it to an interval as wanted.

In the long term I'm open to rethinking this idea or figuring out a better solution for passing intervals directly to the server.

[kwesterfeld2]

We aren't having a problem selecting intervals, we are having a problem sending a parameter to a query as an interval. I am not sure how your workaround helps.

I think what is suitable here is to send the value as a string to pg (as the driver did befor) since the setString() api is used. Converting the value to an interval when PG can do this better simply makes the driver less compatible.

[kdubb]

Sorry, for parameters cast the parameter placeholder. ?::interval::varchar (actually with PostgreSQL the server might just accept ?::varchar). It will send it to the server as text.

[kwesterfeld2]

good idea—worth a shot!

KURT WESTERFELD // Yellowbrick Data

---

From: Kevin Wooten @.> Sent: Saturday, August 14, 2021 5:14:02 PM To: impossibl/pgjdbc-ng @.> Cc: Kurt Westerfeld @.>; Mention @.> Subject: Re: [impossibl/pgjdbc-ng] Driver throws java.time.format.DateTimeParseException (#552)

Sorry, for parameters cast the parameter placeholder. ?::interval::varchar (actually with PostgreSQL the server might just accept ?::varchar). It will send it to the server as text.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fimpossibl%2Fpgjdbc-ng%2Fissues%2F552%23issuecomment-898959489&data=04%7C01%7Ckurt.westerfeld%40yellowbrick.com%7Ca38d8881f0de4b9332b208d95f6870ca%7C912d4ba33f81477a90c6caa2ee5521db%7C0%7C0%7C637645724453400525%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=AwXDqJ%2FLZLKUQ73pyakAdBKInqzBIwJicFx5dskqrH0%3D&reserved=0, or unsubscribehttps://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAJC6YIVGSARGJJRMYVCAIZDT43MBVANCNFSM46TW2TVQ&data=04%7C01%7Ckurt.westerfeld%40yellowbrick.com%7Ca38d8881f0de4b9332b208d95f6870ca%7C912d4ba33f81477a90c6caa2ee5521db%7C0%7C0%7C637645724453410511%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=SDE%2FKrYXFl6hU6E4balOY5C2Y6iLPtD8kQVAAcPrKvo%3D&reserved=0.