通过golang从mysql中查询datetime字段,本想参考之前普通类型查询,只要将变量类型修改为time.Time就可以:
rows,err :=db.Query("select login_time from user where id=1")forrows.Next(){varlastLoginTime time.Timeerr =rows.Scan(&lastLoginTime)iferr !=nil{panic(err)}fmt.Println(lastLoginTime)}
运行代码并没有得到预期的结果,运行出错,err信息为:
sql:Scanerror on column index 1:unsupported driver ->Scanpair:[]uint8 ->*time.Time
通过google得出的解决方法是先将datetime的字面值赋值给string变量。然后通过time.Parse(layout, value)转换为time.Time类型
varlastLoginTime stringerr =rows.Scan(&lastLoginTime)loginTime,_ :=time.Parse("2006-01-02 15:04:05",lastLoginTime)fmt.Println(loginTime)
运行结果为
2018-05-2213:22:51+0000UTC
这里为UTC时间,如果需要使用当地时区可以使用time.ParseInLocation(layout, value, loc)指定当地时区
DefaultTimeLoc:=time.LocalloginTime,err :=time.ParseInLocation("2006-01-02 15:04:05",lastLoginTime,DefaultTimeLoc)
完整代码
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
)
func main() {
db, err := sql.Open("mysql", "root:root@tcp(localhost:3306)/golang?charset=utf8")
checkErr(err)
rows, err := db.Query("select id,login_time from user where id=1")
checkErr(err)
for rows.Next() {
var id int
var lastLoginTime string
err = rows.Scan(&id, &lastLoginTime)
checkErr(err)
DefaultTimeLoc := time.Local
loginTime, err := time.ParseInLocation("2006-01-02 15:04:05", lastLoginTime, DefaultTimeLoc)
checkErr(err)
fmt.Println(loginTime)
}
db.Close()
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}