Reasonable support for java.time.Duration -> Interval conversion

[timboudreau]

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 a java.time.Duration and convert it to an Interval (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 to Interval 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()
        );
    }

[timboudreau]

Here - this would make a fine addition to Interval, and should not have accuracy problems:

    static Interval fromDuration(java.time.Duration duration) {
        long seconds = duration.toSeconds();
        long minutes = seconds / 60;
        seconds -= minutes * 60;
        long hours = minutes / 60;
        minutes -= hours * 60;
        long days = hours / 24;
        hours -= days * 24;
        double micro = duration.getNano() / 1000D;
        return new Interval(0, 0, (int) days, (int) hours, (int) minutes, (int) seconds, (int) round(micro));
    }

And optionally, in the other direction, though months and years are necessarily fuzzy quantities (could return an object that indicates if the conversion is inexact, or an Optional<Duration> that only returns a result if the conversion is exact - though that might be more annoying than useful).

    public Duration toDuration(Interval ival) {
        return java.time.Duration.ofDays(365 * getYears())
                .plus(Duration.ofSeconds((long) (getMonths() * 2.628e+6)))
                .plus(Duration.ofDays(getDays()))
                .plus(Duration.ofHours(getHours()))
                .plus(Duration.ofMinutes(getMinutes()))
                .plus(Duration.ofSeconds(getSeconds()))
                .plus(Duration.ofNanos(1000L * getMicroseconds()));
    }