Skip to content

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

@timboudreau

Description

@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()
        );
    }

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions