2016/11/07 13:40:15 sql: Scan error on column index 5: unsupported Scan, storing driver.Value type []uint8 into type *[]time.Time

[anoopbhat]

I've got a struct

type Record struct { Parent string
Filename string
LastModificationTime time.Time
Size int Mode string
ConsistencyDate []time.Time }

in my table, the consistencydate column is an array of timestamp without timezone.

I'm pulling the data out of the database and here's the error I'm logging

2016/11/07 13:40:15 sql: Scan error on column index 5: unsupported Scan, storing driver.Value type []uint8 into type *[]time.Time

Can't figure out how to resolve this. Sorry if this is the wrong forum for this.

[tamird]

Please see the comment on https://godoc.org/database/sql#Rows.Scan - []time.Time is not supported.

[anoopbhat]

@tamird I see. How else could i do pull that data out and store it into a []time.Time? Thank you.

[tamird]

I'm not sure if it would work, but you can try to introduce a type alias of []time.Time and implement sql.Scanner on it. That said, Go's database/sql doesn't support arrays to my knowledge, so I'm not sure how well that would work out.

On Mon, Nov 7, 2016 at 2:03 PM, Anoop notifications@github.com wrote:

@tamird https://github.com/tamird I see. How else could i do pull that data out and store it into a []time.Time? Thank you.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/lib/pq/issues/536#issuecomment-258929552, or mute the thread https://github.com/notifications/unsubscribe-auth/ABdsPLRN1gvpLATiaRVb2g2cNikW0n95ks5q73XlgaJpZM4Krk8i .

[freeformz]

lib/pq provides support for the array versions of most of what driver.Value supports. Maybe we should also support []time.Time as well?

[tamird]

Ah, I was ignorant of that functionality. Yes, that's probably worth adding.

[adam-hanna]

@anoopbhat here is my current solution:

rows, err := db.Query(`sql goes here`, &params)
if err != nil {
    log.Printf("err querying: %v\n", err)
    panic(err)
}
defer rows.Close()

for rows.Next() {
    var (
        id   uuid.UUID
        foo  []uint8
        bar  time.Time
    )
    if err := rows.Scan(&id, &foo, &bar); err != nil {
        log.Printf("err scanning: %v\n", err)
        panic(err)
    }

    s := string(foo)
    s = strings.Replace(s, "{", "", -1)
    s = strings.Replace(s, "}", "", -1)
    s = strings.Replace(s, "\"", "", -1)
    z := strings.Split(s, ",")
    var tmpFoo []time.Time
    for _, v := range z {
        t, _ := time.Parse("2006-01-02 15:04:05-07", v)
        tmpFoo = append(tmpFoo, t)
    }
}

[dhui]

The best solution may be to implement a TimeArray in pq, similar to the Bool,Int,Float Arrays. But looking at the other array implementations in pq, implementing an array that works with time.Time may be quite involved.

In the meanwhile, using my own time type and time array type seems to be working (see below).

import (
    "database/sql/driver"
    "errors"
    "time"
)

import (
    "github.com/lib/pq"
)

// ErrParseData signifies that an error occured while parsing SQL data
var ErrParseData = errors.New("Unable to parse SQL data")

// PgTime wraps a time.Time
type PgTime struct{ time.Time }

// Scan implements the sql.Scanner interface
func (t *PgTime) Scan(val interface{}) error {
    switch v := val.(type) {
    case time.Time:
        t.Time = v
        return nil
    case []uint8: // byte is the same as uint8: https://golang.org/pkg/builtin/#byte
        _t, err := pq.ParseTimestamp(nil, string(v))
        if err != nil {
            return ErrParseData
        }
        t.Time = _t
        return nil
    case string:
        _t, err := pq.ParseTimestamp(nil, v)
        if err != nil {
            return ErrParseData
        }
        t.Time = _t
        return nil
    }
    return ErrParseData
}

// Value implements the driver.Valuer interface
func (t *PgTime) Value() (driver.Value, error) { return pq.FormatTimestamp(t.Time), nil }

// PgTimeArray wraps a time.Time slice to be used as a Postgres array
// type PgTimeArray []time.Time
type PgTimeArray []PgTime

// type PgTimeArray []pq.NullTime

// Scan implements the sql.Scanner interface
func (a *PgTimeArray) Scan(src interface{}) error { return pq.GenericArray{a}.Scan(src) }

// Value implements the driver.Valuer interface
func (a *PgTimeArray) Value() (driver.Value, error) { return pq.GenericArray{a}.Value() }

[Strum355]

@dhui any reason you didnt open a PR with this? Seems trivial enough that it should get merged