Search code examples
mysqlgotimeunix-timestampgoogle-cloud-sql

What is best practice when storing RFC3339 time in a MySQL database?

I have data in a Google Cloud Datastore NoSQL database. The objects in this database contain fields of type Time.time, specifically RFC3339 format (YYYY-MM-DDThh:mm:ssTZD). I am moving some of this data into a Google Cloud SQL database and I am wondering what is considered best practice when storing RFC3339 formatted time data in a MySQL database.

This post strongly suggests the following:

All date and time columns shall be INT UNSIGNED NOT NULL, and shall store a Unix timestamp in UTC.

So, should I just make use of Golang's func (t Time) Unix() and store the data that way? What are some alternative approaches to storing the data? What kinds of issues might I run into if I don't heed the poster's advice?

Solution

  • If 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, _ := sql.Open("mysql", "root:@/?parseTime=true")

var myTime time.Time
row, err := db.QueryRow("SELECT current_timestamp()")

row.Scan(&myTime)
fmt.Println(myTime)