Working with DB datetime/date columns in Go

📅 Fri, May 26, 2017 ⏱️ One-minute read

This post shows how to work with DATETIME`` / DATEdatabase columns and use Go standardtime.Timeavoiding manual string parsing. This article contains examples using 2 packages:database/sqlandgithub.com/go-sql-driver/mysql`.

Retrieve nullable time field using NullTime type

MySQL, PostgreSQL drivers in Go provide this nullable type which represents a time.Time that may be NULL. NullTime implements the Scanner interface so it can be used as a scan destination:

1
2
3
4
5
6
7
8
var nt mysql.NullTime
err := db.QueryRow("SELECT time FROM foo WHERE id = ?", id).Scan(&nt)

if nt.Valid {
   // use nt.Time
} else {
   // NULL value if Valid is false
}

Use parseTime=true

You can ask the driver to scan DATE and DATETIME automatically to time.Time, by adding parseTime=true to your connection string (DSN).

1
2
3
4
5
6
7
db, err := sql.Open("mysql", "root:@/?parseTime=true")

var myTime time.Time

db.QueryRow("SELECT current_timestamp()").Scan(&myTime)

fmt.Println(myTime.Format(time.RFC3339))

Limitation: TIME column type

Note that parseTime=true does not automatically convert the MySQL TIME column type to time.Time. The TIME type represents a time of day or duration, not a full timestamp. You should scan TIME columns into []byte or string and handle the parsing manually if needed.

Feedback

As always, please reach out to me on X with questions, corrections, or ideas!