How to parse time from database

Question

I am using golang and I am trying to read time from mysql and I am getting the following error.

var my_time time.Time
rows, err := db.Query("SELECT current_time FROM table")
err := rows.Scan(&my_time)

The error I am getting is

 unsupported driver -> Scan pair: []uint8 -> *time.Time

How can I fix this?

Dew

You can Scan the value into a string variable, then get myTime by parsing that string. – Morphinism

Please add following information: type of database, type of column data – Herbivore

@VincenzoMaggio type of database is mysql, the column is just current_time and id. – Dew

Answer 1

Assuming you're using the go-sql-driver/mysql you can ask the driver to scan DATE and DATETIME automatically to time.Time, by adding parseTime=true to your connection string.

See https://github.com/go-sql-driver/mysql#timetime-support

Example code:

db, err := sql.Open("mysql", "root:@/?parseTime=true")
if err != nil {
    panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
}
defer db.Close()

var myTime time.Time
rows, err := db.Query("SELECT current_timestamp()")

if rows.Next() {
    if err = rows.Scan(&myTime); err != nil {
        panic(err)
    }
}

fmt.Println(myTime)

Notice that this works with current_timestamp but not with current_time. If you must use current_time you'll need to do the parsing youself.

This is how you do custom parsing:

First, we define a custom type wrapping []byte, that will automatically parse time values:

type rawTime []byte

func (t rawTime) Time() (time.Time, error) {
    return time.Parse("15:04:05", string(t))
}

And in the scanning code we just do this:

var myTime rawTime
rows, err := db.Query("SELECT current_time()")

if rows.Next() {
    if err = rows.Scan(&myTime); err != nil {
        panic(err)
    }
}

fmt.Println(myTime.Time())

Hartzke

Alternatively, you can use a mysql.NullTime in your struct. This will parse the time irrespective of the connection string setting, and has the additional benefit of handling null values correctly. – Crosswalk

@RichSutton Careful with this. MySQL time data type can also be used to store durations of time (i.e. more than 24 hours). In those cases, you'll need to parse the duration yourself. So, it really depends on how you're using the field. – Prom

How about timestamp type in mysql, is it the same? – Bearish