How to .Scan() a MySQL TIMESTAMP value into a time.Time variable?

Question

I have this 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 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 | [email protected] |   NULL |            0 | 2014-10-28 15:37:44 | N      |
+----+------------+-----------------------+--------+--------------+---------------------+--------+
1 row in set (0.00 sec)

---

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

{1 alakhazamm [email protected] 0 0 {0 0 <nil>} }

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

Am I missing something important or is it a bug/missing feature of the library?

Answer 1

I know this is an old question however I was missing this parameter in my Open call:

parseTime=true

See here

Answer 2

In addition to answer by @incognick, here is exactly you can do (add parseTime=true):

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

If there is a possibility of timestamp/datetime be null, in that case you should use scan parameter as sql.NullTime instead of time.Time.

Using sql.NullTime will provide you option to check if scanned time is null or not using var.Valid flag. You can use time with var.Time if it is valid and not null.

Answer 3

I've found the cause of the error.

Since rating is NULL in the database, the scanner gave the error

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

I've updated the database row and now usr.date_registered and usr.online hold the correct values.

I guess I'll have to make the MySQL field NOT NULL and just use -1 to indicate a non-initialised value.