Convert pgtype.Interval to time.Duration

[nagylzs]

I would like to convert a pgtype.Interval value into a time.Duration value. The pgtype.Interval struct has these fields:

type Interval struct {
    Microseconds int64
    Days         int32
    Months       int32
    Valid        bool
}

The PostgreSQL interval type has a special format for storing intervals, this is from the current docs ( https://www.postgresql.org/docs/current/datatype-datetime.html ):

---

Internally, interval values are stored as three integral fields: months, days, and microseconds. These fields are kept separate because the number of days in a month varies, while a day can have 23 or 25 hours if a daylight savings time transition is involved. An interval input string that uses other units is normalized into this format, and then reconstructed in a standardized way for output, for example:

SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
               interval
---------------------------------------
 3 years 3 mons 700 days 133:17:36.789

Here weeks, which are understood as “7 days”, have been kept separate, while the smaller and larger time units were combined and normalized.

So I guess it means that when Months is not zero, then this cannot be converted to a duration, because in that case it does not have a fixed length. Am I right?

But when Months==0, then I suppose the correct way to convert it into a duration is:

duration := time.Microsecond * time.Duration(interval.Microseconds) + time.Hour*time.Duration(24*interval.Days)

I'm proposing this new method:

func (interval *Interval) Duration() (time.Duration, error) {
  if (!interval.Valid) {
    return time.Duration(0), errors.New("null value cannot be converted to duration")
  }
  if (interval.Months > 0) {
    return time.Duration(0), errors.New("does not have a fixed duration")
  }
  return time.Microsecond * time.Duration(interval.Microseconds) + time.Hour*time.Duration(24*interval.Days), nil
}

or something similar. It would be handy, I think this is the right place to put this implementation, and I suspect that it would save some time by eliminating questions about "how to convert interval to duration".

[nagylzs]

I'm not sure about leap seconds though, sometimes the length of a day is not fixed either.

[jackc]

As you surmise, there is no guaranteed lossless way to convert a PostgreSQL interval to a Go time.Duration. But what can be done is already done. You don't need to convert a pgtype.Interval. You can scan directly into a time.Duration. Interval months are considered to be 30 days and days are considered to be 24 hours.

[nagylzs]

I guess scanning into *time.Duration will also work, and will scan NULL into nil value. All right, I can live with this. Thank you.