How to represent PostgreSQL interval in Go

Question

How to represent PostgreSQL interval in Go?

My struct looks like this:

type Product struct {
    Id              int
    Name            string
    Type            int
    Price           float64
    Execution_time  ????
}

The execution_time field on my database is interval.

Answer 1

The best answer I've come across is to use bigint in your schema, and implement Value & Scan on a wrapper type for time.Duration.

// Duration lets us convert between a bigint in Postgres and time.Duration
// in Go
type Duration time.Duration

// Value converts Duration to a primitive value ready to written to a database.
func (d Duration) Value() (driver.Value, error) {
    return driver.Value(int64(d)), nil
}

// Scan reads a Duration value from database driver type.
func (d *Duration) Scan(raw interface{}) error {
    switch v := raw.(type) {
    case int64:
        *d = Duration(v)
    case nil:
        *d = Duration(0)
    default:
        return fmt.Errorf("cannot sql.Scan() strfmt.Duration from: %#v", v)
    }
    return nil
}

Unfortunately, you'll sacrifice the ability to do interval arithmetic inside queries - unless some clever fellow wants to post the type conversion for bigint => interval.

Answer 2

If you're OK with conforming to the time.Duration limits and you need only seconds accuracy you could:

• Create the table with a SECOND precision ... someInterval INTERVAL SECOND(0), ...

• Convert INTERVAL into seconds: SELECT EXTRACT(EPOCH FROM someInterval) FROM someTable;

• Use time.Duration::Seconds to insert data to prepared statements

Answer 3

One solution is to wrap the time.Duration type in a wrapper type, and on it provide implementations of sql.Scanner and driver.Valuer.

// PgDuration wraps a time.Duration to provide implementations of
// sql.Scanner and driver.Valuer for reading/writing from/to a DB.
type PgDuration time.Duration

Postgres appears to be quite flexible with the format provided when inserting into an INTERVAL column. The default format returned by calling String() on a duration is accepted, so for the implementation of driver.Value, simply call it:

// Value converts the PgDuration into a string.
func (d PgDuration) Value() (driver.Value, error) {
    return time.Duration(d).String(), nil
}

When retrieving an INTERVAL value from Postgres, it returns it in a format that is not so easily parsed by Go (ex. "2 days 05:00:30.000250"), so we need to do some manual parsing in our implementation of sql.Scanner. In my case, I only care about supporting hours, minutes, and seconds, so I implemented it as follows:

// Scan converts the received string in the format hh:mm:ss into a PgDuration.
func (d *PgDuration) Scan(value interface{}) error {
    switch v := value.(type) {
    case string:
        // Convert format of hh:mm:ss into format parseable by time.ParseDuration()
        v = strings.Replace(v, ":", "h", 1)
        v = strings.Replace(v, ":", "m", 1)
        v += "s"
        dur, err := time.ParseDuration(v)
        if err != nil {
            return err
        }
        *d = PgDuration(dur)
        return nil
    default:
        return fmt.Errorf("cannot sql.Scan() PgDuration from: %#v", v)
    }
}

If you need to support other duration units, you can start with a similar approach, and handle the additional units appropriately.

Also, if you happen to be using the GORM library to automatically migrate your table, you will also want to provide an implementation of GORM's migrator.GormDataTypeInterface:

// GormDataType tells GORM to use the INTERVAL data type for a PgDuration column.
func (PgDuration) GormDataType() string {
    return "INTERVAL"
}