go-sql-driver / go-sql-driver/mysql

year 0 in time.Time is valid but always throws an error

Open
#1,223 26 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
15.3k
Forks
2.3k
Avg merge
2h 23m
Merged PRs (30d)
10

Description

### Issue description
after upgrade from v1.5.0 to v.1.6.0, a `time.Time` representing an actual time without a date cannot be inserted into a column of type `TIME` any longer, as it has year 0 set. funnily, this value was previously even retrieved from the DB. year 0 is also in general not an invalid `time.Time` value for real dates.

from https://golang.org/pkg/time/#Parse :
>Elements omitted from the value are assumed to be zero or, when zero is impossible, one, so parsing "3:04pm" returns the time corresponding to Jan 1, year 0, 15:04:00 UTC (note that because the year is 0, this time is before the zero Time). Years must be in the range 0000..9999.

the following recently added line throws an error when the year is 0: https://github.com/go-sql-driver/mysql/blob/417641ad42910e50863a0caf4740ce319262f2f9/utils.go#L285

as far as i can see, `appendDateTime()`, the function which contains this line, is called twice, and both times when the given `time.Time` does not have its zero value as determined by `IsZero()`. however, the zero value for a `time.Time` is in year 1, see https://golang.org/pkg/time/#Time.IsZero :
>IsZero reports whether t represents the zero time instant, January 1, year 1, 00:00:00 UTC.

what is the reason the range was picked to be `[1, 9999]` here?

if you can confirm this is actually an issue, i'm willing to try and fix it :slightly_smiling_face:

### Example code
```go
package main

import (
"database/sql"
"fmt"
"time"

_ "github.com/go-sql-driver/mysql"
)

func main() {
db, err := sql.Open("mysql", "user:pw@instance:3306/db")
if err != nil {
panic(err)
}

if _, err := db.Exec("CREATE TEMPORARY TABLE IF NOT EXISTS `time_test` (`id` int not null, `some_time` time not null);"); err != nil {
panic(err)
}

if _, err := db.Exec("insert into `time_test` values(1, '08:30:30');"); err != nil {
panic(err)
}

var someTime string
row := db.QueryRow("select some_time from `time_test` where id = 1;")
if err := row.Scan(&someTime); err != nil {
panic(err)
}

fmt.Printf("this worked: %v\n", someTime)

mysqlTimeFormat := "15:04:05"
parsedTime, err := time.Parse(mysqlTimeFormat, someTime)
if err != nil {
panic(err)
}

// this works in 1.5.0, panics in 1.6.0
if _, err := db.Exec("update `time_test` set some_time = ? where id = 1;", parsedTime); err != nil {
panic(err)
}

fmt.Println("success!")
}

```

### Error log
the error i receive is the error from the line i specified above:
`panic: year is not in the range [1, 9999]: 0`

### Configuration
*Driver version (or git SHA):* v1.6.0

*Go version:* 1.16.2

*Server version:* MySQL 8.0.20

*Server OS:* official mysql docker container, iirc its based on debian but should be irrelevant.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.