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
}