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