unsupported Scan, storing driver.Value type []uint8 into type *time.Time

Question

I have difficulty querieing for users, which is defined as:

type User struct {
    ID           int       `db:"id" json:"id"`            
    UserName     string    `db:"username" json:"username"` 
    Email        string    `db:"email" json:"email"`
    CreatedAt    time.Time `db:"created_at" json:"created_at"`
    StatusID     uint8     `db:"status_id" json:"status_id"`
    Deleted      uint8     `db:"deleted" json:"deleted"`
... 
}

And the table in MariaDB is defined as:

+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| username     | varchar(50)      | NO   |     | NA                |                |
| email        | varchar(255)     | NO   |     | NULL              |                |
| created_at   | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| status_id    | tinyint(1)       | NO   |     | 0                 |                |
| deleted      | tinyint(1)       | NO   |     | 0                 |                |
...              |

However when I query it like:

func GetUsers(c *gin.Context) {
    var users []model.User
    err := shared.Dbmap.Select(&users, "SELECT * FROM user")

    if err == nil {
        c.JSON(200, users)
    } else {
        fmt.Println("%v \n", err)
        c.JSON(http.StatusInternalServerError, gin.H{"error": "no user(s) in the table or problem in the query"})
    }

    // curl -i http://127.0.0.1:8080/api/v1/users
}

I get this error:

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

while there are some rows in the table.

I have also tried created_at as timestamp but still get the same error.

So I'm left clueless as what wrong here? How can I fix it?

P.S. Though my question turned out to have the same answer as this but here the context is different (sqlx instead of go-sql-driver/mysql). Also since here the error is the subject it probably more searchable for people who google the same error. So perhaps this worth keeping as a separate question.

Mirtamirth

Possible duplicate of Go Parse Time From Database – Hypostasize

Answer 1

Alright, I found the solution, thanks this answer. The problem goes by adding ?parseTime=true to the db mapper. Like this:

db, err := sqlx.Connect("mysql", "myuser:mypass@tcp(127.0.0.1:3306)/mydb?parseTime=true")

Mirtamirth

It is also possible to add a custom time type with a custom scanner if your driver does not support time parsing or for other reasons: github.com/mattn/go-sqlite3/issues/190#issuecomment-343341834 – Noncombatant

This only applies to MySQL. what about Postgresql? – Tarriance

Answer 2

In my case, changing to

db, err := sql.Open("mysql", "root:@/?parseTime=true")

resolved problem.

Franni

Answer 3

In my case, I had a similar problem but for my particular usecase, it was a struct with a select which didn't work, since I only needed to select data, then I proceeded to simply:

change the data type from time.Time to string

This solved my problem, and I was able to get the data from the database. So perhaps it is not the best solution, but this is what worked for me.

Mr Karlom's solution also worked for me, this is just an alternative method.

Graph

Answer 4

Another Solution if you are using gorm pkg

db, err := gorm.Open("root:root@tcp(localhost:3306)db?parseTime=true"), &gorm.Config{})

This will fix the issue

Gall

Answer 5

Faced this issue with a psql database.

The order in which you run your scan matters. It should follow the fields order in your database.

For example

    query := `CREATE TABLE IF NOT EXISTS accounts (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
        encrypted_password VARCHAR(255),
    number BIGINT,
    balance BIGINT,
        deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP
    )`

func scanIntoAccount(rows *sql.Rows) (*Account, error) {
    var account Account
    err := rows.Scan(
        &account.ID,
        &account.FirstName,
        &account.LastName,
        &account.EncryptedPassword,
        &account.Number,
        &account.Balance,
        &account.Deleted,
        &account.CreatedAt,
        &account.UpdatedAt,
    )

    if err != nil {
        return nil, err
    }

    return &account, nil
}

Stilbite