Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
bbigras opened this issue Mar 25, 2015 · 16 comments

Comments

@bbigras
Copy link

bbigras commented Mar 25, 2015

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
Copy link
Owner

mattn commented Mar 26, 2015

datetime('now') has no type affinity.

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

@mattn
Copy link
Owner

mattn commented Mar 26, 2015

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

@bbigras
Copy link
Author

bbigras commented Mar 26, 2015

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
Copy link

gwenn commented Mar 28, 2015

@djhworld
Copy link

djhworld commented Apr 5, 2015

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
Copy link

stevenh commented Feb 20, 2016

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
Copy link

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
Copy link
Owner

mattn commented Apr 15, 2016

I don't understand what is similar.

@gwenn
Copy link

gwenn commented Apr 15, 2016

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
Copy link

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

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

@mattn
Copy link
Owner

mattn commented Jul 11, 2016

it's an option just provided for mysql driver.

@misoul
Copy link

misoul commented Feb 5, 2017

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

@mattn
Copy link
Owner

mattn commented Feb 6, 2017

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
Copy link

?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
Copy link
Owner

mattn commented Nov 10, 2017

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
Copy link
Collaborator

Answered; Closed

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

10 participants