support sql Value and Scan for custom date type

[krhubert]

Hey,

I haven't found a solution that can work out of the box (maybe except interface { Get() interface{} } for Set method, but nothing that can work with AssingTo.
This is why I've created this PR. It would be great to have support for custom dates.

[jackc]

I don't understand the problem that prompted this. Scan() and Value() should work for all types out of the box.

I adapted your test to a standalone example and it works for me:

package main

import (
    "context"
    "database/sql/driver"
    "fmt"
    "log"
    "os"
    "time"

    "github.com/jackc/pgx/v4"
)

type customDate struct {
    t time.Time
}

func (d customDate) Value() (driver.Value, error) {
    return d.t.Format("2006-01-02"), nil
}

func (d *customDate) Scan(src interface{}) (err error) {
    if src == nil {
        d.t = time.Time{}
        return nil
    }

    switch v := src.(type) {
    case int64:
        d.t = time.Unix(v, 0).UTC()
    case float64:
        d.t = time.Unix(int64(v), 0).UTC()
    case string:
        d.t, err = time.Parse("2006-01-02", v)
    case []byte:
        d.t, err = time.Parse("2006-01-02", string(v))
    case time.Time:
        d.t = v
    default:
        err = fmt.Errorf("failed to scan type '%T' into date", src)
    }
    return err
}

func main() {
    ctx := context.Background()

    conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close(ctx)

    dateIn := customDate{t: time.Date(2000, 1, 1, 0, 0, 0, 0, time.Local)}

    var dateOut customDate
    err = conn.QueryRow(ctx, "select $1::date", dateIn).Scan(&dateOut)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println(dateOut.t.GoString())
}

[krhubert]

It works for structs, but it doesn't work for slices. And I created the PR to fix this issue. I should be more clear about that. Check out your examples but with slices:

package main

import (
    "context"
    "database/sql/driver"
    "fmt"
    "log"
    "os"
    "time"

    "github.com/jackc/pgx/v4"
)

type customDate struct {
    t time.Time
}

func (d customDate) Value() (driver.Value, error) {
    return d.t.Format("2006-01-02"), nil
}

func (d *customDate) Scan(src interface{}) (err error) {
    if src == nil {
        d.t = time.Time{}
        return nil
    }

    switch v := src.(type) {
    case int64:
        d.t = time.Unix(v, 0).UTC()
    case float64:
        d.t = time.Unix(int64(v), 0).UTC()
    case string:
        d.t, err = time.Parse("2006-01-02", v)
    case []byte:
        d.t, err = time.Parse("2006-01-02", string(v))
    case time.Time:
        d.t = v
    default:
        err = fmt.Errorf("failed to scan type '%T' into date", src)
    }
    return err
}

func main() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close(ctx)

    dateIn := []customDate{{t: time.Date(2000, 1, 1, 0, 0, 0, 0, time.Local)}}

    var dateOut []customDate
    err = conn.QueryRow(ctx, "select $1::date[]", dateIn).Scan(&dateOut)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println(dateOut[0].t.GoString())
}

output:

2022/10/02 17:00:11 cannot convert {{0 63082278000 0xabe8c0}} to Date in DateArray

[jackc]

Ah. I see now.

Your fix handles the sql.Value / sql.Scanner for date arrays. Unfortunately, the same issue will exist for all other types. And I don't see anyway of making it a general improvement in pgx v4. But I'll merge it. I guess those can be updated on demand.

On the plus side, pgx v5 already handles this in the general case for all types.