unsupported Scan, storing driver.Value type []uint8 into type *time.Time
MysqlGoSqlxMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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")
Solution 2 - Mysql
In my case, I'm change
db, err := sql.Open("mysql", "root:@/?parseTime=true")
Is resolve problem.
Solution 3 - Mysql
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.