Scan TIME into mysql.NullTime error

[Chasiny]

Issue description

hello,I scan TIME into mysql.NullTime. As result, it return error as follow

sql: Scan error on column index 0: invalid time string: 00:00:00

Example code

mysql conn like below

chy:chasiny@tcp(chasiny.com:3306)/test_db?charset=utf8&parseTime=true&loc=Local

findSQL := `select start_time, end_time from discount_merchandise where id = ? and state = 1;`
err := db.QueryRow(findSQL,merchandiseId).Scan(&merchandise.StartTime,&merchandise.EndTime)

start_time and end_time are TIME type in mysql
merchandise.StartTime and merchandise.EndTime are mysql.NullTime type

Error log

sql: Scan error on column index 0: invalid time string: 00:00:00

My experimental method to solve this problem

I modify the function parseDateTime in utils.go

func parseDateTime(str string, loc *time.Location) (t time.Time, err error) {
	base := "0000-00-00 00:00:00.0000000"
	switch len(str) {
	case 10, 19, 21, 22, 23, 24, 25, 26: 
		if str == base[:len(str)] {
			return
		}
        t, err = time.Parse(timeFormat[:len(str)], str)
        
    // case 8 in order to parse the TIME type
	case 8:								// up to HH:MM:SS, support TIME type
		if str == base[11:11+len(str)] {
			return
		}
        t, err = time.Parse(timeFormat[11:11+len(str)], str)
    // end modify

	default:
		err = fmt.Errorf("invalid time string: %s", str)
		return
	}

	if err == nil && loc != time.UTC {
		y, mo, d := t.Date()
		h, mi, s := t.Clock()
		t, err = time.Date(y, mo, d, h, mi, s, t.Nanosecond(), loc), nil
	}

	return
}

I wonder is there any better solution to solve this problem? Or this driver support mysql TIME type (include NULL value)?
Thanks you very much!

Configuration

Driver version (or git SHA): d523deb (tag: v1.4.0)

Go version: go version go1.10.3 windows/amd64

Server version: MySQL 5.6.40

Server OS: Linux

[methane]

Would you create "reproducible" example including whole program and DB schema and data, not only code snippets.
I'm not sure even you're parsing NULL or 00:00:00. I'm not sure your column type is TIME or DATE or DATETIME or TIMESTAMP too.

[Chasiny]

@methane thanks for your reply, I write the demo as follow

Table sql

discount_merchandise sql

CREATE TABLE `discount_merchandise` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`start_time` TIME NULL DEFAULT NULL ,
	`end_time` TIME NULL DEFAULT NULL ,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=100809
;

Record sql

insert 2 records

insert into discount_merchandise(id,start_time,end_time) values (1,'09:00:00','20:00:00');
insert into discount_merchandise(id,start_time,end_time) values (2,NULL,NULL);

Go demo

package main

import (
	"database/sql"
	"github.com/go-sql-driver/mysql"
	"fmt"
)

type Merchandise struct {
	Id int
	StartTime mysql.NullTime
	EndTime mysql.NullTime
}

func main(){
	conn :=  "root:caihongye@tcp(192.168.17.130:3306)/chasiny?charset=utf8&parseTime=true&loc=Local"

	db, err := sql.Open("mysql", conn)
	if err!=nil{
		panic(err)
	}
	defer db.Close()

	var merchandise Merchandise

	err=db.QueryRow("select start_time,end_time from discount_merchandise where id =1 ").Scan(&merchandise.StartTime,&merchandise.EndTime)
	if err!=nil{
		fmt.Println("id 1:",err)
	}

	err=db.QueryRow("select start_time,end_time from discount_merchandise where id =2 ").Scan(&merchandise.StartTime,&merchandise.EndTime)
	if err!=nil{
		fmt.Println("id 2:",err)
	}

}

Program running result:

id 1: sql: Scan error on column index 0: invalid time string: 09:00:00

[methane]

OK, I got it.
NullTime type is nullable variant of Go's time.Time.
We support time.Time for MySQL's DATE and DATETIME, but not for TIME.

Go's time.Time is semantically different from MySQL's TIME. That's why we don't support MySQL's TIME type with NullTime or time.Time.

[Chasiny]

OK, thank you.
So is there any struct in this driver to support MySQL's TIME, or I need to defind a new struct by myself to support MySQL's TIME ?

[methane]

There are no type for it. And I'm not sure about adding it in this module is worth.

MySQL's TIME type has two semantics: "time of a day" and "duration".
But providing types for them which are easy to use from application is clearly out of scope of this project.
But if it's not usable, who is happy with it?

Maybe, utility function like ParseTime(s string) time.Duration which parses string like -123:45:59.123456 is considerable.