INSERT datetime using now() with Go

Question

Using sql database driver

I've been referencing the post above. But having troubling doing an INSERT statement where one of my fields are for datetime. I tried doing "NOW()" but when I check the mysql db, i am seeing 0000-00-00 00:00:00. Anyone know what I can use as the value to properly have it insert the datetime?

Also- I know I can construct datetime using go's time package, but I do not wish to use the timestamp of the machine running Go. I want the datetime of the machine running mysql. Hency, why I used "NOW()".

stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
checkErr(err)

res, err := stmt.Exec("reckhou", "IT", "NOW()")
checkErr(err)

id, err := res.LastInsertId()
checkErr(err)

fmt.Println(id)

Answer 1

The NOW() needs to be part of the SQL text, it's not a bind value.

INSERT userinfo SET username=?,departname=?,created=NOW()

The way you have it coded, MySQL is seeing "NOW()" as a literal string, as if it were any old string like 'foo'. MySQL is casting that string value to a DATETIME (MySQL is expecting a format close to 'YYYY-MM-DD HH:MM:SS', and it's failing to cast that to a valid date. We'd expect either a NULL or a special "zero-date" like you are seeing.

Answer 2

var datetime = time.Now()
dt := datetime.Format(time.RFC3339)
_, err = DatabaseHandle.Exec("INSERT into userinfo(id, date) VALUES (?,?)", 0, dt)

Beware that this will set the current time of the server running the go code, which might be different from the MySQL Server (might not be the same timezone, etc.)