As of Dolt version 1.78.0, we have a new value for zero time. Zero time refers to datetime values converted from 0, false, and the timestamps 0000-00-00 and 0000-00-00 00:00:00. This is a breaking change that affects any previous values of zero time stored in Dolt, but we decided the benefits of a new value of zero time outweighed the risks of a breaking change.

So why did we mess with time? We’ll explain in this blog post.

Previous Value of Zero Time#

Zero time in MySQL has the timestamp 0000-00-00 00:00:00. However, this timestamp is impossible to achieve in Go, the language that Dolt and go-mysql-server are written in, which requires non-zero values in the month and day fields of a timestamp. As a workaround, we used time.Unix(-62167219200, 0).UTC(), which has the timestamp 0000-01-01 00:00:00, to represent zero time in Dolt and go-mysql-server.

The Problems#

The problems with our previous value of zero time were exposed by a GitHub issue filed by TheoristCoder. While in MySQL, DAYOFMONTH(0) returned 0, Dolt was returning 1; this was then affecting the result of joins that joined on the result of DAYOFMONTH. Upon further investigation, many of other datetime-related function calls, such as MONTH(0) and DAY(0), incorrectly returned 1 instead of 0. This bug was due to us converting 0 to zero time and then extracting the day or month from the timestamp – since the date of zero time was January 1, we were returning 1 for the day and 1 for the month.

Our initial approach was to include a check for zero time in our datetime-related functions – if the input was zero time, we would return the correct value to match MySQL, rather than extracting values from the timestamp. But what if the input time had a timestamp of 0000-01-01 00:00:00? It would match our value of zero time, yet it’s a non-zero timestamp in MySQL, where MONTH and DAY should actually be returning 1. Furthermore, we saw that our value of zero time was causing bugs such as DATE(‘0000-01-01’) returning 0000-00-00 instead of 0000-01-01.

We realized then that in order to ensure the correctness of our datetime-related functions, we needed a new value for zero time that did not have a timestamp that conflicted with a non-zero timestamp in MySQL.

New Value of Zero Time#

We decided to set our new value of zero time to time.Date(0, 0, 0, 0, 0, 0, 0, time.UTC).

ZeroTime = time.Date(0 /* year */, 0 /* month */, 0 /* day */, 0 /* hour */, 0 /* min */, 0 /* sec */, 0 /* nsec */, time.UTC /* loc */)

Since Go requires non-zero values for the month and day, this underflows to have a timestamp of -0001-11-30 00:00:00. Negative years are not valid in MySQL, so this new timestamp for zero time does not conflict with any non-zero timestamps in MySQL.

With this new value of zero time, we did an audit of our datetime-related functions to ensure that the value they return for inputs of 0, false, and 0000-01-01 matched what MySQL returns. We are pleased to say that our datetime-related functions now match MySQL’s behavior.

There is an exception, and that’s with functions related to days of the week and inputs with dates before 0000-03-01. This is because Go considers year 0 a leap year where 0000-02-29 is a valid date while MySQL does not. Due to this extra day, the days of the week are off by one. For example, 0000-01-01 is a Sunday in MySQL but a Saturday in Go and Dolt. Year 0 is not a real year in the Gregorian calendar anyways so we don’t foresee this being a big issue.

What Breaks#

Any values of zero time stored in a Dolt database before the release of 1.78.0 will still have the old value of zero time, meaning they still have a timestamp of 0000-01-01 00:00:00, and will no longer be considered zero time. While we understand that this can be an inconvenience, this was a tradeoff we considered when it came to ensuring the correctness of our datetime-related functions.

If this change is causing any issues for you, feel free to file an issue on GitHub or visit our Discord server. We’d be happy to help!