Negative time when parse api.SQL_C_TYPE_TIME

[xhit]

In a SELECT statement where there are a column with data type TIME, the time.Time returned is a time that returns negative because in api.SQL_C_TYPE_TIME the year, month and day are 0.

When I parse the date with a custom format, for example:
x.Format("2006-01-02 15:04:05")

The date string returned for a time 01:01:01 in the column is:
-0001-11-30 01:01:01.000000000

Others drivers avoid this case with a default year, month and day like 1 in all, or 1900,1,1, etc...

• Why need to be a positive date?
  Because other apps can be have trouble parsing a negative date. For example integration software.

[akhilravuri1]

Hi @xhit

Can you provide a sample program to reproduce the issue and what is the actual data type of the column in the database ?

Thanks,
Akhil

[xhit]

The datatype is TIME.

Go returns a negative time when time is less than zero date, but this cannot be parsed. See golang/go#7136

To handle this, the PR #77 fix this adding 1 year, one month and one day (the zero date 0001-01-01) like other drivers, this solve the problem parsing the time ignoring the date.

Example code:

package main

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

	_ "github.com/ibmdb/go_ibm_db"
)

func main() {
	//connecto to DB
	db, err := connectDB2("192.168.43.150", "SAMPLE", "SCHEMATEST", "db2admin", "pass", 50000)
	if err != nil {
		panic(err)
	}

	defer db.Close()

	tableName := "ISSUE76"

	queryDrop := `drop table "` + tableName + `";`

	db.Exec(queryDrop)

	queryCreate := `CREATE TABLE "` + tableName + `" ("time_column" TIME);`

	_, err = db.Exec(queryCreate)
	if err != nil {
		panic(err)
	}

	defer db.Exec(queryDrop)

	queryInsert := `insert into "` + tableName + `" ("time_column") values (?);‪`

	_, err = db.Exec(queryInsert, time.Time{})
	if err != nil {
		panic(err)
	}

	var t time.Time
	if err := db.QueryRow(`SELECT "time_column" FROM ` + tableName).Scan(&t); err != nil {
		panic(err)
	}

	// the time returned is -0001-11-30 00:00:00
	fmt.Println(t)
}

//connect to db2 database
func connectDB2(host, database, schema, user, password string, port int) (*sql.DB, error) {

	//Password cannot contains ;
	connString := fmt.Sprintf("HOSTNAME=%s;DATABASE=%s;PORT=%d;UID=%s;PWD=%s", host, database, port, user, password)

	db, err := sql.Open("go_ibm_db", connString)

	if err != nil {
		return nil, err
	}

	err = db.Ping()

	if err != nil {
		return nil, err
	}

	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)

	//set the schema
	if len(schema) > 0 {
		_, err = db.Exec("SET CURRENT SCHEMA ?", schema)

		if err != nil {
			return nil, err
		}
	}

	return db, nil
}