Add support for scanning postgresql `interval` type into `time.Duration`

[stevvooe]

Just thought I'd file this as a nice to have.

[kisielk]

Not sure if this is possible. The range of time.Duration in go is +/- 290 years. In postgresql it's considerably larger: +/- 178000000 years. Go's time.Duration is a 4 byte int64 whereas Postgres uses 12 bytes to represent an interval.

[kisielk]

Also http://code.google.com/p/go/issues/detail?id=4954 was recently closed, so there will be no support for time.Duration in database/sql. In that case we just need to make our own type that implements the scanner interface. We could create an Interval type that's an alias of [12]byte and has many of the same semantics as time.Duration,

[AlekSi]

👍

[cdipaolo]

Not sure if this is still up for debate but it would be nice to have in my use case instead of having to convert the string to a duration by hand in go.

👍

[johto]

The point still stands: you can't convert an interval to a Duration without losing information. What was said above only touches the surface: postgres' intervals don't store just seconds, but also the number of days and months. So you could have an interval of "1 month 1 day 34 seconds", which doesn't map into a time.Duration at all.

[cdipaolo]

Yeah after further research yesterday I found that out. You could
potentially map to a custom struct but that wouldn't be very idiomatic or
clean
On Wed, May 20, 2015 at 8:12 AM Marko Tiikkaja notifications@github.com
wrote:

The point still stands: you can't convert an interval to a Duration
without losing information. What was said above only touches the surface:
postgres' intervals don't store just seconds, but also the number of days
and months. So you could have an interval of "1 month 1 day 34 seconds",
which doesn't map into a time.Duration at all.

—
Reply to this email directly or view it on GitHub
#78 (comment).

[stevvooe]

Perhaps, we could re-specify this issue and have lib/pq provide common time types that interoperate with the postgresql timestamp and interval types. The main issue right now is that its hard to map any postgresql timestamp or interval types. This is a massive gap in functionality and hard to get right for each individual project.

[johto]

Perhaps, we could re-specify this issue and have lib/pq provide common time types that interoperate with the postgresql timestamp and interval types. The main issue right now is that its hard to map any postgresql timestamp or interval types. This is a massive gap in functionality and hard to get right for each individual project.

I think what we could do is provide a struct which stores the values for all the individual components in an interval. You could then access said components through the struct or you could ask for the struct to convert itself into a time.Duration using the same rules extract(epoch from interval) in postgres does. It shouldn't be more than a SMOP for someone to implement.

[AlekSi]

I think this issue should be renamed – as pointed above it's not possible to map interval to time.Duration.

[stevvooe]

@johto Your suggestion sounds reasonable, as it will make specialized field mapping easier to work with.

@AlekSi The edge case here can be detected as an error. It doesn't make sense to omit support for interval with time.Duration when the vast majority of use cases don't need +/- 178000000. If the interval overflows time.Duration, we could simply throw an error. Another type can be provided in lib/pq for use in cases where ranges larger than +/- 292 years are actually required.

[kostya-sh]

@stevvooe interval and time.Duration are incompatible types. time.Duration represents duration between two moments in time, while interval represents interval between two dates. 1 day interval is not the same as 24 hours duration: http://play.golang.org/p/OLF9L2EsX6

[stevvooe]

@kostya-sh Thanks for bringing up the difference between calendrical calculations and exact differentials. Just as there may be overflow of the time.Duration type, there may be intervals, such as those with components that need an anchor to evaluate, that don't map to time.Duration.

However, there are entire classes of interval values that are completely compatible. For example, let's take this:

postgres=# select extract(epoch from i), extract(days from i) from (select interval '1000000 seconds' as i) as foo;
 date_part | date_part
-----------+-----------
   1000000 |         0
(1 row)

As we can see, the seconds component of this interval is more than compatible with time.Duration. There are no days or years.

But, yes, I agree that interval expressed in terms of days is incompatible:

postgres=# select extract(epoch from i), extract(days from i), extract(years from i) from (select interval '1 year>
 date_part | date_part | date_part
-----------+-----------+-----------
  33544800 |        23 |         1
(1 row)

This would have to be mapped to a type that exposes the calendrical components, if required. Oddly enough, the use of epoch does evaluate the interval without binding it to a date, which could be compatible with time.Duration.

My point is that this isn't quite as absolute as claimed. The overlap in domains of time.Duration and interval tends to be useful in practice such that omitting support for time.Duration hurts the usability of this package. As it stands now, there is now way to extract this from pq, which is disappointing.

At least, let's take the first step of having an interval type.

[kostya-sh]

I think adding pq.Interval type is the best solution here. I believe it is reasonable for a driver package to provide Go types for database specific data types.

[ear]

Hi. I'd love to have a type to work with for Postgres Intervals. Just in case you decide not to put it in lib/pq can you give an example of how to implement it in user code if at all possible? Thank you.

[teepark]

I've started a project to tackle this with an Interval type that fully matches postgres's semantics and a Duration alias that converts according to the EXTRACT(EPOCH) behavior. Total WIP at the moment but it's starting to successfully scan intervals.

https://github.com/teepark/pqinterval