-
Notifications
You must be signed in to change notification settings - Fork 206
Description
Describe the feature
Working with the standard java.time.Duration
class in postgres is unnecessarily painful.
Use cases
I ran into this issue with a simple query that invokes a stored procedure:
select incident_count,risk_level from safety_score_v2($1::interval, $2::latitude, $3::longitude)
where the code supplying the interval
is handed a java.time.Duration
. Having done some testing in psql
, Postgres supports ISO-8601 duration strings perfectly well in a query - i.e. P1Y::interval
gets you an interval of a year. Great! So, I try to pass Tuple.of(duration.toString(), ...)
.
vertx-pg-client helpfully (and silently!) replaces the duration with NULL
. Very surprising when my queries start returning tens of thousands of result.
Tried a few incantations of ::varchar
and similar trying to head-fake vertx-pg-client into doing the right thing. No luck.
Solutions:
- First,
Tuple
should really just accept ajava.time.Duration
and convert it to anInterval
(or whatever) under the hood - Second, a well-formed ISO-8601 string for an interval should be accepted
- Third, there should be some sane way of converting
Duration
toInterval
in a constructor, so if none of the above is acceptable, there is at least a low-complexity way of doing that (could be as simple as creating an interval with the number of seconds and microseconds in the duration - though it would be more polite to anyone reading queries from the database log to extract those elements that don't have varying lengths - i.e. days, hours, minutes, seconds, microseconds)
Contribution
I might consider it.
Quick'n'dirty, unpretty code to do the job with some necessary inaccuracy around years and months (but good enough for my current purposes):
static Interval durationToInterval(java.time.Duration duration) {
// Vertx's duration class really ought to do this itself.
double seconds = duration.toSeconds();
long years = (long) (seconds / 31540000.0010112);
long yearsRemainder = (long) (seconds - (years * 31540000.0010112));
long months = (long) (yearsRemainder / 2628336.2137828967534);
long monthsRemainder = yearsRemainder - (long) (months * 2628336.2137828967534);
long days = (long) (monthsRemainder / 86410.958906879997812);
return new Interval(
(int) years,
(int) months,
(int) days,
duration.toHoursPart(),
duration.toMinutesPart(),
duration.toSecondsPart()
);
}