Go-sqlite3: Scan cannot convert sqlite datetime to go *time.Time

Created on 28 Aug 2014 · 8Comments · Source: mattn/go-sqlite3

Scan() fails for datetime columns with go type *time.Time.

package main

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

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

func main() {
        os.Remove("boooo.db")
        db, err := sql.Open("sqlite3", "boooo.db")
        if err != nil {
                log.Fatal("Failed to open database:", err)
        }
        defer db.Close()
        db.Exec(`create table foo (id integer, dt datetime);`)
        rows, err := db.Query(`select 7, datetime('now')`)
        if err != nil {
                log.Fatal("Failed to call db.Query:", err)
        }
        defer rows.Close()
        for rows.Next() {
                var id int
                // time.Time fails
                var dt *time.Time
                // string ok
                //var dt string
                err = rows.Scan(&id, &dt)
                if err != nil {
                        log.Fatal("Failed to db.Query:", err)
                }
                fmt.Println(id, dt)
        }
}

Outputs:

2014/08/27 20:00:54 Failed to db.Query:sql: Scan error on column index 1: unsupported driver -> Scan pair: []uint8 -> *time.Time

Source

aletheia7

👍1

All 8 comments

Try this.

package main

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

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

func main() {
        os.Remove("boooo.db")
        db, err := sql.Open("sqlite3", "boooo.db")
        if err != nil {
                log.Fatal("Failed to open database:", err)
        }
        defer db.Close()
        db.Exec(`create table foo (id integer, dt datetime)`)
        db.Exec(`insert into foo values (1, datetime('now'))`)
        rows, err := db.Query(`select id, dt from foo`)
        if err != nil {
                log.Fatal("Failed to call db.Query:", err)
        }
        defer rows.Close()
        for rows.Next() {
                var id int
                // time.Time fails
                var dt time.Time
                // string ok
                //var dt string
                err = rows.Scan(&id, &dt)
                if err != nil {
                        log.Fatal("Failed to db.Query:", err)
                }
                fmt.Println(id, dt)
        }
}

select 7, datetime('now')

It seems the second field doesn't have type name.

mattn on 28 Aug 2014

👍1

Expression (like datetime('now')) has no type affinity by default: http://www.sqlite.org/datatype3.html#expraff
Except for cast: cast(datetime('now') as text) may work (not tested).
This is a limitation of the database/sql/driver.

gwenn on 29 Aug 2014

So you need to use string instead of time.Time . And parse the string.

mattn on 30 Aug 2014

👎5

gwenn on 30 Aug 2014

I've had success by entering the datetime field as a unix timestamp

package main

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

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

func main() {
    os.Remove("boooo.db")
    db, err := sql.Open("sqlite3", "boooo.db")
    if err != nil {
        log.Fatal("Failed to open database:", err)
    }
    defer db.Close()
    db.Exec(`create table foo (id integer, dt datetime);`)
    db.Exec(`insert into foo values (1, strftime('%s', 'now'))`)
    rows, err := db.Query(`select * from foo`)
    if err != nil {
        log.Fatal("Failed to call db.Query:", err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        // time.Time fails
        var dt *time.Time
        // string ok
        //var dt string
        err = rows.Scan(&id, &dt)
        if err != nil {
            log.Fatal("Failed to db.Query:", err)
        }
        fmt.Println(id, dt)
    }
}

Output: