使用ziutek / mymysql选择NULL时间戳

Question

I have the following Google Go and MySql, where the date_update field in question often comes back as a NULL value.

var date_update time.Time
query := `SELECT date_update FROM users WHERE user_id=?`
err := conn.QueryRow(query, user_id).Scan(&date_update)

When I run this query and happen to receive one of the null values for the field, I receive the error unsupported driver -> Scan pair: <nil> -> *time.Time

The way I handle this with other data types (for example INT) is with

SELECT IFNULL(integer_field, 0) FROM table

But it doesn't seem to work with timestamps or datetime. Any ideas?

Answer 1

This is a google groups discussion that discusses a problem almost identical to yours.

This is the section is taken from the README of the go-sql-drivers/mysql repository.

time.Time support

The default internal output type of MySQL DATE and DATETIME values is []byte which allows you to scan the value into a []byte, string or sql.RawBytes variable in your programm.

However, many want to scan MySQL DATE and DATETIME values into time.Time variables, which is the logical opposite in Go to DATE and DATETIME in MySQL. You can do that by changing the internal output type from []byte to time.Time with the DSN parameter parseTime=true. You can set the default time.Time location with the loc DSN parameter.

Caution: As of Go 1.1, this makes time.Time the only variable type you can scan DATE and DATETIME values into. This breaks for example sql.RawBytes support.

Alternatively you can use the NullTime type as the scan destination, which works with both time.Time and string / []byte.

You either have to add parseTime=true to your DSN in order for it to parse into a time.Time automatically or use NullTime.

---

Using mymysql

You could try selecting the row with a specific id & then use one of the following functions:

• Row.TimeErr

• Row.ForceLocalTime

• Row.ForceTime