Go-sqlite3: 'sql: Scan error on column index 0: unsupported driver -> Scan pair: []uint8 -> *time.Time' with datetime('now')

[bbigras]

sqlite3_column_decltype() returns NULL when it's an expression.

I'm not sure it's a bug.

I was thinking that maybe go-sqlite3 could guess that the string is a datetime but that could cause problem when someone want a text timestamp on purpose.

It could be easier to do the work on the user side since we know if we expect a string or a datetime/date/timestamp.

func TestDateTimeNow(t *testing.T) {
    tempFilename := TempFilename()
    db, err := sql.Open("sqlite3", tempFilename)
    if err != nil {
        t.Fatal("Failed to open database:", err)
    }
    defer db.Close()

    var d time.Time
    err = db.QueryRow("select datetime('now')").Scan(&d)
    if err != nil {
        t.Fatal("Failed to scan datetime:", err)
    }
}

--- FAIL: TestDateTimeNow (0.02s)
        sqlite3_test.go:917: Failed to scan datetime: sql: Scan error on column index 0: unsupported driver -> Scan pair: []uint8 -> *time.Time
FAIL

[mattn]

datetime('now') has no type affinity.

http://www.sqlite.org/datatype3.html#expraff

[mattn]

So you need to use cast(datetime('now') as text)

[bbigras]

I still have the problem if I cast to text.

func TestDateTimeNow(t *testing.T) {
    tempFilename := TempFilename()
    db, err := sql.Open("sqlite3", tempFilename)
    if err != nil {
        t.Fatal("Failed to open database:", err)
    }
    defer db.Close()

    var d time.Time
    err = db.QueryRow("SELECT cast(datetime('now') as text)").Scan(&d)
    if err != nil {
        t.Fatal("Failed to scan datetime:", err)
    }
}

--- FAIL: TestDateTimeNow (0.01s)
        sqlite3_test.go:960: Failed to scan datetime: sql: Scan error on column index 0: unsupported driver -> Scan pair: []uint8 -> *time.Time

It seems the type was already text.

sqlite> SELECT typeof(datetime('now'));
text
sqlite> SELECT typeof(cast(datetime('now') as text));
text

[gwenn]

https://groups.google.com/forum/#!topic/golang-nuts/P6Wrm_uVvJ0

[djhworld]

I've had to work around this using the following code

type mystruct struct {
  createdDate time.Time
}

mystructs := []mystruct{}
rows, err := database.Queryx(`select created_date as createddate from my_table`)

//created date comes in as a text string in the format "YYYY-MM-DD"
for rows.Next() {
    var createdDate string
    err = rows.Scan(&createdDate)
    t, _ := time.Parse("2006-01-02", createdDate)
    mystructs = append(mystructs, mystruct{t})
}

[stevenh]

I just noticed that if you create the table with the column type datetime (which appears to map to text) scanning works, if its created as an integer it doesn't.

Not sure if this is your problem too?

[DeedleFake]

I've got a similar problem. I've got a table with two columns, id integer and login timestamp. I'm trying to use select id, min(login) and scan it into an int64 and a time.Time. I get this error:

sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *time.Time

A normal select id, login works fine, however. Is there a different SQL syntax I should be using, is this expected behavior, or is this a bug? I can use order by login limit 1 with QueryRow() to just get the first row, but that seems kind of ineffiecient.

[mattn]

I don't understand what is similar.

[gwenn]

min(login) has no declared type.
So this code is not executed.
Currently, any expression (except simple reference to a column) returning a date (i.e. datetime('now'), min(login), ...) cannot be scanned as a time.Time.
The driver needs to know the destination/go type (i.e., time.Time)...

[weilixu7]

setting the DNS param solved this problem for me. :)

user:pass@tcp(endpoint:3306)/dbname?parseTime=true

[mattn]

it's an option just provided for mysql driver.

[misoul]

"?parseTime=true" does it for me. I guess this would be very common.. no? Kinda a catcha that easily costs people hours.

[mattn]

No, go's database/sql has three layers.

• drivers
• database/sql
• user application

Most of type conversion is done in database/sql. So driver can't know what type of pointers are passed into the driver.

for rows.Next() {
    var v time.Time
    rows.Scan(&v) // driver cant know v is time.Time
}

[samuelkaufman]

?parseTime=true is only supported by the MySQL driver (as of the time I'm writing this at least).
i have not read the source so take this with a grain of salt, but from experimenting and reading docs/issues it seems like this driver looks at the schema of the table in deciding how to scan.

If you use a (explicit) text data type for the column, time.Time values will serialize fine into the database, but will throw an error if you try to select (scan) back into Go.

If you use a 'datetime' or 'timestamp' column type for your time.Time column, it will go in fine both ways, and the underlying data type will remain the same, as a 'text.'

If you SELECT typeof(mytimecol) FROM mytable you will see 'text' for every row.

[mattn]

Or you can scan the time.Time with making simple type converter. See https://golang.org/pkg/database/sql/#Scanner

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

type MyTime time.Time

func (t *MyTime) Scan(v interface{}) error {
    // Should be more strictly to check this type.
    vt, err := time.Parse("2006-01-02 15:04:05", string(v.([]byte)))
    if err != nil {
        return err
    }
    *t = MyTime(vt)
    return nil
}

func main() {
    db, err := sql.Open("sqlite3", ":memory:")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    rows, err := db.Query(`select datetime('now')`)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var dt time.Time
        err = rows.Scan((*MyTime)(&dt))
        if err != nil {
            log.Fatal(err)
        }
        fmt.Println(dt)
    }
}

[GJRTimmer]

Answered; Closed