how parseTime in sql connections work with time.Time in Go

During testing of a feature I had to create objects that generate the current time, store it in the database, read it back out again, and make sure that the times on both the object that I stored and the object I got out of the database had the same time.

Along the way I encountered a lot of weird things. The first one was that for some reason the time I put in and the one I got out was shifted by an hour, so I went looking into the different parts of what might cause this.

Briefly going to talk about the internals of time.Time, its timezone handling, then the database connections’s and server’s timezone handling, what that means for storing data, and a bunch of gotchas at the end.

What happens in time.Now()?

The structure of a time.Time struct is that it has three different bits:

1. something called a “wall clock”, which is what we use when telling time. It’s what you see when you look at the clock on the wall, assuming the clock follows DST changes automatically. This won’t be used for telling the duration between two different times ordinarily because if you look at the time at 1:58am on 25th October in London, and then look at the time an hour later, it will say 1:58am with a difference of 0 between the wall clocks. British Summer Time ends at 2am on 25th October 2020, so 2am -> 1am on that day.
2. something called the monotonic clock. This is initialised the moment the new time instance is created, and is used for time differences. Some methods on time will strip the monotonic clock from the instance: .AddDate, .Round, and .Truncate for example.
3. and a location. The location is only used for display purposes, it does not change the actual value of the time. By default the location is going to be based on the local timezone of the machine.

What is “the local timezone”?

I don’t know how this works on a Windows system, but on a Unix one (I’m on a mac) it first looks at the TZ environment variable which is a timezone string, like UTC, or Europe/Berlin, or Asia/Tokyo.

If the TZ is not set, then it grabs the location called localtime from the /etc/ directory on the system.

/etc/localtime/ is a symlink to the actual location binary. On a mac you’re going to set it in the system preferences:

The above setting results in this:

$ ls -la /etc/localtime
lrwxr-xr-x  1 root  wheel  36  8 Oct 00:21 /etc/localtime -> /var/db/timezone/zoneinfo/Asia/Tokyo

Local time in a docker container

The way to set the timezone of a docker container is to set its TZ environment variable.

What happens in an SQL connection?

There are two timezones that we need to deal with in a MySQL database:

1. the timezone of the system, which is what the mysql server is going to use
2. and the timezone of the connection itself.

SQL connection

Setting the connection’s timezone is the easier one. The dsn (data source name) can contain a bunch of parameters. A full dsn I would use in my code looks like this:

const dsn = "test:test@(localhost:3310)/test?parseTime=true&loc=Asia%2FTokyo"

Here I’m setting the connection to Asia/Tokyo.

SQL server

If the mysql server is running locally, it most probably uses the same timezone as your system. Otherwise you can configure it with the --timezone command line flag, or issue a SET GLOBAL timezone = "Asia/Tokyo". See the MySQL documentation on timezone support for more exhaustive info.

This is important because of the TIMESTAMP column we’re using.

The TLDR of a TIMESTAMP is that it stores the time as UTC, and presents you the value based on your connection to the database. That also means that when storing the value, whatever string is coming in, MySQL is going to assume it’s in the timezone of the connection. See my earlier article on the nuances of TIMESTAMP and DATETIME in MySQL where I learned about timezones and MySQL.

As an example: 2020-10-07 12:00:00 with a connection timezone of Europe/London the underlying UTC value is 2020-10-07 11:00:00.

The same 2020-10-07 12:00:00 value with connection timezone of Asia/Tokyo will have an underlying UTC value of 2020-10-07 03:00:00.

MySQL server timezone is going to be important for current timestamp.

Current local time in London is 2020-10-07 17:39:00, the SQL connection is also Europe/London, SQL server is Asia/Tokyo. When I execute an INSERT INTO table (ts) VALUES (current_timestamp), and immediately query it back out, I will get 2020-10-08 01:39:00.

Let’s talk about parseTime and SQL statements

The importance of the parseTime in the dsn is that given a prepared statement and a time.Time argument, the driver is going to make sure that the time.Time is going to be turned into the necessary format and timezone before storing it.

It will also mean that when scanning values out of a table that has a timestamp (or datetime, date, time) column will be unfurled into a time.Time instance instead of a []byte.

Consider this code snippet (error handling and imports omitted):

// connection is America/Phoenix
db, _ := sql.Open("mysql", mysqlDSN)

// table has an auto-increment id and a timestamp colum ts
insert, _ := db.Prepare("INSERT INTO table (ts) VALUES (?)")

// ts is now 2020-10-07 12:00:00+01:00 for BST
ts := time.Now()

results, _ := insert.Exec(ts)
id, _ := results.LastInsertId()

// then we open another connection, this time the tz is Europe/London
db2, _ := sql.Open("mysql", mysqlDSN2)

select, _ := db.Prepare("SELECT * FROM table WHERE id = ?")

// we get the same row out that we inserted previously
rows, _ := select.Exec(id)

var id int64
var gotTime time.Time

for rows.Next() {
	_ = rows.Scan(&id, &gotTime)
}

// compare the times
fmt.Printf("in: %s\nout: %s",
    ts.In(time.UTC).Format(time.RFC3339Nano),
    gotTime.In(time.UTC).Format(time.RFC3339Nano))

// this will print:
in: 2020-10-07 11:00:00.703337Z
out: 2020-10-07 11:00:00Z

Note that the nanosecond (the 703337 part) is missing from the “out” time. That’s because when we created a time with time.Now(), Go managed to measure the nanosecond at which time it was created.

However putting that into the database with the MySQL date format (YYYY-MM-SS HH:MM:SS) does not have the resolution to store nanoseconds, so consequently when reading the timestamp out the nanoseconds can’t be recreated¹.

statement.Exec internal for time.Time parsing

It’s going to loop through all the arguments, check their type, and if it’s a time.Time, it’s going to first change the timezone location of it to whatever the connection’s timezone is, and then format it to a MySQL timestamp format and pass that to the database as a string, so the correct UTC time is going to be stored.

In essence it’s doing this:

ts.In(connection.Timezone).Format(mysqlDateTimeFormat)

If you want to look at the actual code in version 1.5.0 of the mysql driver: https://github.com/go-sql-driver/mysql/blob/v1.5.0/packets.go#L1105-L1122.

rows.Scan internal for time.Time parsing

Internally the driver (github.com/go-sql-driver/mysql) is going to return a core sql.Rows result set.

The .Scan(args ...interface{}) method on that struct is going to call a function called convertAssignRows for each column, where it’s going to take the destination, the pointer to the type you’re scanning into, and the source, the result that came back from the database for that column, and match them up with conversions.

Practically this means that if the connection has parseTime=true on the dsn, any value with TIMESTAMP, DATETIME, TIME, DATE will end up as a time.Time value with the location set as the timezone of the connection, just like above.

If then the variable you’re scanning into is also a time.Time, then you just get that value back straight. If you’re scanning a time.Time into a string, then you’re going to get back the RFC3339Nano representation of the stored value.

This is the code in core Go version 1.15.2 that’s responsible for doing that conversion.

Important to note that if parseTime=true is not on the dsn, then the value of a TIMESTAMP column is going to return as a []byte, at which point you can’t scan it into a time.Time variable.

What does this mean for testing?

All of the above mean the following:

• time.Now() will Just Work™ with everything. What you put in is what you’re going to get out because all three timezones are accounted for along the way (your local system’s, the SQL connection’s, and the SQL server’s)
• when you’re creating a new time based on a string with time.Parse, make sure the string you’re parsing is in UTC timezone as time.Parse is just a shorthand for time.ParseInLocations with time.UTC as a location
• when you’re creating a new time based on a string with time.ParseInLocation, make sure you create a time.Location that corresponds with the values in the time string
• when comparing whether a time.Time that you put in and got out of a database is the same, make sure to round to the nearest second. A TIMESTAMP column does not have the resolution to store nanoseconds, so when getting that out that’s lost, and time1.Equal(time2) is going to fail¹
• in general let the Go api do its thing when it comes to time operations. Use the available methods, and only mess around with timezones when creating a time, or when you’re preparing to display it someplace

Hope you found this somewhat useful. If you have questions, or want to send me an email, find me on twitter via @javorszky, or send me an email on gabor (at) javorszky (dot) co (dot) uk.

addenda

[1] You can technically create a TIMESTAMP column that supports fractional seconds up to 6 digits. In that case you would still need to .Round(time.Microsecond) to get rid of the nanosecond issues when comparing with time.Time.Equal. That said using fractional seconds and truncate mode in SQL can cause time drifts. By default when MySQL gets a string 2020-10-07 12:33:12.778 to be stored in a column capable of taking 2 digits’ worth of fractional seconds, it’s going to store 2020-10-07 12:33:12.78. If truncate mode is turned on, the same insert produces 2020-10-07 12:33:12.77, which is going to be fun if you do a .Round(time.Millisecond * 10) and compare them because they’re going to be off by 0.01. See https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html for more fractional seconds in MySQL.

Wall clocks photo by Luis Cortes on Unsplash

Library atrium photo by Tobias Fischer on Unsplash

Double rainbow photo by Ashley Ross on Unsplash