Support of INTERVAL type

[dimarudik]

Dear Philipp,
Is it possible to support INTERVAL type?
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
BR,
Dima

[bytefish]

Yes, it can be done, but I can't give a timeline of course. If you want to take a try at implementing it in the meantime, here is how I would do it and it's not really complicated.

The Npgsql implementation for an Interval is here:

• https://github.com/npgsql/npgsql/blob/main/src/Npgsql/Internal/Converters/Temporal/IntervalConverters.cs

The relevant line is:

    protected override void WriteCore(PgWriter writer, NpgsqlInterval value)
    {
        writer.WriteInt64(value.Time);
        writer.WriteInt32(value.Days);
        writer.WriteInt32(value.Months);
    }

So we would provide a class Interval (Npgsql has a NpgsqlInterval), which is a simple DTO:

public readonly struct NpgsqlInterval : IEquatable<NpgsqlInterval>
{
    /// <summary>
    /// Constructs an <see cref="NpgsqlInterval"/>.
    /// </summary>
    public NpgsqlInterval(int months, int days, long time)
        => (Months, Days, Time) = (months, days, time);

    /// <summary>
    /// Months and years, after time for alignment.
    /// </summary>
    public int Months { get; }

    /// <summary>
    /// Days, after time for alignment.
    /// </summary>
    public int Days { get; }

    /// <summary>
    /// Remaining time unit smaller than a day, in microseconds.
    /// </summary>
    public long Time { get; }

This can be easily translated to a Java POJO.

You would then add an IValueHandler in the pgsql/handlers folder, like this:

// Licensed under the MIT license. See LICENSE file in the project root for full license information.

package de.bytefish.pgbulkinsert.pgsql.handlers;

import java.io.DataOutputStream;

public class IntervalHandler extends BaseValueHandler<Interval> {

    @Override
    protected void internalHandle(DataOutputStream buffer, final Interval value) throws Exception {
        buffer.writeLong(value.getTime());
        writer.writeInt(value.getDays());
        writer.writeInt(value.getMonths());
    }

    @Override
    public int getLength(Interval value) {
        return 16;
    }
}

Then register it here:

• https://github.com/PgBulkInsert/PgBulkInsert/blob/master/PgBulkInsert/src/main/java/de/bytefish/pgbulkinsert/pgsql/handlers/ValueHandlerProvider.java

And wire it up in the AbstractMapping:

• https://github.com/PgBulkInsert/PgBulkInsert/blob/master/PgBulkInsert/src/main/java/de/bytefish/pgbulkinsert/mapping/AbstractMapping.java

And that's it.

[dimarudik]

thx, will try

[bytefish]

No need to close!

[bytefish]

@dimarudik I did an initial implementation, which can be used like this:

• https://github.com/PgBulkInsert/PgBulkInsert/blob/18daa96e999411d0710b8deb8e7b1bb711b8b507/PgBulkInsert/src/test/java/de/bytefish/pgbulkinsert/test/pgsql/handlers/PgBulkInsertTest.java#L167

It works, but I think the API is pretty ugly. What do you think would be a good Java Type for an Interval?

[bytefish]

I think Java 8 has not equivalent to an Interval.

The Joda Time Interval is probably the best:

• https://www.joda.org/joda-time/apidocs/org/joda/time/Interval.html

But I won't take a dependency on it. We could add a small plugin to use it, just like I did with PostgisBulkInsert.

[dimarudik]

@bytefish Could you please show the test with SimpleRowWriter

[bytefish]

This is how to use it:
https://github.com/PgBulkInsert/PgBulkInsert/blob/000fddd968d843fbc36e6049245b6857514abd80/PgBulkInsert/src/test/java/de/bytefish/pgbulkinsert/test/row/SimpleRowWriterTest.java#L52

[bytefish]

It's been released as 8.1.4. 👌