Go and MySQL timestamps
By R. S. Doiel, 2022-12-12
The Go sql package
provides a nice abstraction for working with SQL databases. The
underlying drivers and DBMS can present some quirks that are SQL dialect
and driver specific such as the MySQL driver. Sometimes that
is not a big deal. MySQL can
maintain a creation timestamp as well as a modified timestamp easily via
the SQL schema definition for the field. Unfortunately if you need to
work with the MySQL timestamp at a Go level (e.g. display the timestamp
in a useful way) the int64 provided via the driver isn’t compatible with
the int64
used in Go’s time.Time
. To work
around this limitation I’ve found it necessary to convert the MySQL
timestamp to a formatted string using DATE_FORMAT
and from the Go side convert the formatted string into a
time.Time
using time.Parse()
. Below is some
Golang pseudo code showing this approach.
// Format used by MySQL strings representing date/times
const MySQLTimestamp = "2006-01-02 15:04:05"
// GetRecordUpdate takes a configuration with a db attribute previously
// opened and an id string returning a record populated with id and updated values where updated is an attribute of type time.Time. We use MySQL's
// `DATE_FORMAT()` function to convert the timestamp into a string and
// Go's `time.Parse()` to convert the string into a `time.Time` value.
func GetRecordUpdate(cfg, id string) {
stmt := `SELECT id, DATE_FORMAT(updated, "%Y-%m-%d %H:%i:%s") FROM some_tabl WHERE id = ?`
row, err := cfg.db.Query(stmt, id)
if err != nil {
return nil, err
}
defer row.Close()
record := new(Record)
if row.Next() {
var updated string
if err := row.Scan(&record.ID, &updated); err != nil {
return nil, err
}
record.Updated, err = time.Parse(MySQLTimestamp, updated)
if err != nil {
return nil, err
}
}
err = row.Err()
return record, err
}