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

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)
	}
}

setting the DNS param solved this problem for me. 😃

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

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
}

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?

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})
}

?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.

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)…

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.