How is the .Scan () MySQL TIMESTAMP value in time.Time variable?

Question

I have a Go code:

package main import ( "fmt" "database/sql" _"github.com/go-sql-driver/mysql" "time" ) type User struct { id uint32 name string email string rating uint8 subscription uint8 date_registered time.Time online string } // main entry point func main() { // setup db connection db, err := sql.Open("mysql", "user:@tcp(127.0.0.1:3306)/c9?parseTime=true") if err != nil { fmt.Println(err) } defer db.Close() // query rows, err := db.Query("SELECT * FROM users WHERE id = ?", 1) if err != nil { fmt.Println(err) } defer rows.Close() usr := User{} for rows.Next() { err := rows.Scan(&usr.id, &usr.name, &usr.email, &usr.rating, &usr.subscription, &usr.date_registered, &usr.online) if err != nil { fmt.Println(err) } } fmt.Println(usr) err = rows.Err() if err != nil { fmt.Println(err) } } 

This is what I get from the MySQL console:

 mysql> describe users; +-----------------+---------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+-------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | email | varchar(50) | NO | | NULL | | | rating | tinyint(3) unsigned | YES | | NULL | | | subscription | tinyint(3) unsigned | NO | | 0 | | | date_registered | timestamp | NO | | CURRENT_TIMESTAMP | | | online | char(1) | NO | | N | | +-----------------+---------------------+------+-----+-------------------+----------------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM users; +----+------------+-----------------------+--------+--------------+---------------------+--------+ | id | name | email | rating | subscription | date_registered | online | +----+------------+-----------------------+--------+--------------+---------------------+--------+ | 1 | alakhazamm | [e-mail] | NULL | 0 | 2014-10-28 15:37:44 | N | +----+------------+-----------------------+--------+--------------+---------------------+--------+ 1 row in set (0.00 sec) 

---

After .Scan() , fmt.Println(usr) prints

 {1 alakhazamm [e-mail] 0 0 {0 0 <nil>} } 

The last two fields of the structure are wrong, but I have no idea why. I tried using date_registered string in the structure definition, but after .Scan() I get an empty string. I also read in the driver docs that ?parseTime=true parses MySQL DATE and DATETIME values ​​over time. Time, but they don’t mention TIMESTAMP, which I am currently using.

Am I missing something important or is it a bug / missing library function?

Answer 1

I found the cause of the error.

Since rating is NULL in the database, the scanner returned an error

sql: Scanning error on column index 3: converting string "nil" to uint8: strconv.ParseUint: parsing "nil": invalid syntax

I updated the database row and now usr.date_registered and usr.online holding the correct values.

I think I need to make the MySQL NOT NULL field and just use -1 to indicate an uninitialized value.

Answer 2

I know this is an old question, but this parameter was missing in my open call:

 parseTime=true 

Look here