Here at DoltHub, one of our projects is go-mysql-server, a MySQL-compatible database engine that’s written in Go and powers Dolt, the world’s first version-controlled database. In go-mysql-server, we often rely on Go standard libraries, but sometimes, we have to work around them to get the same behavior as MySQL. Previously, I blogged about updating our value of zero time to be more aligned with MySQL. In this blog, I’ll explain why we had to move away from using Go’s func (time.Time) Sub and the considerations we had to make for Go’s implementations of time in the time.Time struct when implementing our own time difference function.

The Bug#

TIMESTAMPDIFF is a function in MySQL that takes three arguments (a time unit and two datetime expressions) and calculates the difference in the specified unit between the two datetime expressions. I had noticed that Dolt and go-mysql-server’s TIMESTAMPDIFF function was not returning the correct values for times that were sufficiently far apart and that it would return the same incorrect value for a given unit argument.

Our implementation of TIMESTAMPDIFF would convert the datetime expression arguments into two time.Time structs (time1 and time2), get the difference between the two times by calling time2.Sub(time1), and convert the difference to the correct unit. The root of the bug was the call to func (time.Time) Sub.

The Problem with func (time.Time) Sub#

The function signature for func (time.Time) Sub looks like this:

func (t Time) Sub(u Time) Duration

The function calculates the difference between Time t and Time u as a Duration. A Duration is an int64 representing nanoseconds. As an int64, its largest value is 9,223,372,036,854,775,807 nanoseconds, or approximately 292 years. This explained why the result of TIMESTAMPDIFF seemed to be stuck at the same value for a given unit.

Because TIMESTAMPDIFF needed to work for any time arguments between 0000-01-01 00:00:00 and 9999-12-31 23:59:59.999999, we could no longer rely on func (time.Time) Sub to calculate time differences.

Calculating the Difference in Microseconds#

Thankfully, MySQL doesn’t care about nanoseconds – the smallest time unit that MySQL handles is microseconds. The largest time difference value we needed to handle was between 0000-01-01 00:00:00 and 9999-12-31 23:59:59.999999, which is 315,569,433,599,999,999 microseconds, a number small enough to fit inside an int64. So integer overflow was no longer something we needed to consider.

We calculate the difference between two times in microseconds by converting them to microseconds since Unix epoch using func (Time) UnixMicro and then taking the difference.

func microsecondsDiff(time1, time2 time.Time) int64 {
	return time2.UnixMicro() - time1.UnixMicro()
}

We’ve recently been very invested in Dolt’s performance compared to MySQL’s, and converting the times to microseconds since Unix epoch didn’t seem like the most performant solution. Why couldn’t we just calculate the times in microseconds directly, without the conversion? Why the extra step? Well, this comes down to Go’s implementation of time.Time.

A Tale of Two (and Sometimes Three) Epochs#

In order to calculate the difference between two times, they need to be normalized to the same epoch. An epoch is a fixed time reference point, and times in computing are typically stored as numbers representing some unit of time elapsed since an epoch. If two times do not have the same epoch, you’re not going to get the correct difference simply by subtracting them. It’s just math (the proof is left as an exercise to the reader).

In the time.Time struct, Go uses two different epochs depending on whether a time is monotonic or not: January 1, 1885 for monotonic time and January 1, 0001 for other time. January 1, 1885 seems to be a reference to Back to the Future II.

Two epochs! What a mess!

Because of these two different epochs, Go doesn’t have exported public functions that expose time values directly.

When calculating time differences using func (Time) Sub, Go uses func (Time) sec to normalize times to seconds since the January 1, 0001 epoch. func (Time) sec, combined with func (Time) Nanosecond to calculate microseconds, is what we want to use to be the most performant, but it’s an unexported private function that can’t be used outside of the time package. It seems like Go wants to keep their underlying epochs secret. Instead, we have to rely on the limited exported public functions, and func (Time) UnixMicro is our best option, despite its runtime – it first converts times using func (Time) sec before converting them again to time since the Unix epoch.

Conclusion#

In the end, we were able to fix our TIMESTAMPDIFF implementation to return the correct values, even if Go had to do some extra time conversions under the hood. If you’re interested in learning more about the time package, you can read the documentation or dig into the source code.

Found a bug in Dolt or go-mysql-server? File an issue, or join our Discord server!