sql.NullTime can't get expected value

[jinger7281]

Issue description

table construct

CREATE TABLE `t_order` (
     `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
     `order_code` VARCHAR(32) NOT NULL  COLLATE 'utf8mb4_general_ci',
     `original_amount` BIGINT(20) NULL DEFAULT NULL,
     `create_time` DATETIME NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
INSERT INTO `t_order` (`id`, `order_code`, `original_amount`, `create_time`) VALUES(1, '1093726990682361857', 1, '2020-12-11 09:52:06');

I'm not good at English, if there are some syntax mistake, forgive me, thanks In the Example Code section if CreateTime's type set to sql.NullTime, the log.Println will get

{1 1093726990682361857 1 {0001-01-01 00:00:00 +0000 UTC true}}

if type set to mysql.NullTime, it will get

{1 1093726990682361857 1 {2020-12-11 09:52:06 +0000 UTC true}}

Example code

package main

import (
	"database/sql"
	"log"

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

// Order the data object struct of order table
type Order struct {
	ID             uint64       `json:"id" sql:"id"`
	OrderCode      string       `json:"order_code" sql:"order_code"`
	OriginalAmount uint64       `json:"original_amount" sql:"original_amount"`
	CreateTime     sql.NullTime `json:"create_time" sql:"create_time"`
}

func main() {
	db, err := sql.Open("mysql", "root:root@tcp(localhost)/test")
	if err != nil {
		log.Panic(err)
	}

	stmt, err := db.Prepare("SELECT id, order_code, original_amount, create_time FROM t_order LIMIT 1")

	if err != nil {
		log.Panic(err)
	}

	rows, err := stmt.Query()

	if err != nil {
		log.Panic(err)
	}

         if err != nil {
		log.Panic(err)
	}
	orderDO := Order{}
	rows.Next()
	rows.Scan(&orderDO.ID, &orderDO.OrderCode, &orderDO.OriginalAmount, &orderDO.CreateTime)
	log.Println(orderDO)
}

Error log

If you have an error log, please paste it here.

Configuration

*Driver version (or git SHA): require github.com/go-sql-driver/mysql v1.5.0 // indirect

*Go version: go version go1.15.6 windows/amd64 go version go1.14.6 windows/amd64 go version go1.13.15 windows/amd64

*Server version: MariaDB 10.5.4

*Server OS: Version: Windows 10 Pro 64Bit VersionNo: 20H2 OS Version: 19042.685 Experience: Windows Feature Experience Pack 120.2212.551.0

[jinger7281]

Go version: go version go1.15.5 freebsd/amd64

OS version: FreeBSD 12.1-RELEASE-p10

has the same result

[methane]

sql.NullTime.Scan() doesn't support []byte. For now, parseTime=true option is required.

@julienschmidt @arnehormann @shogo82148 @vmg Should we change the parseTime to true in next version?

[jinger7281]

@methane Thanks a lot, the problem is solved

[dolmen]

@jinger7281 Not related to the issue, but I recommend to use type TIMESTAMP instead of DATETIME for a column used to save a fixed point in time.

[tangxinfa]

NullTime works partially, when we scan from rows its works, the timezone settings in DSN is used, we get the timestamp from database with right timezone.

But when we insert a record into db, the timezone is missing, for example:

conn.Query("insert into `hello` (`notnull_ts`, `null_ts`) values(?,?)", t1, t2)

the type of t1 is time.Time, the type of t2 is sql.NullTime.

Will generate the following SQL for MySQL to execute:

INSERT INTO `hello` (`notnull_ts`, `null_ts`) VALUES ('2020-11-08 09:45:00','2021-01-06 02:47:36.718266021 +0000 UTC')

The SQL is built by func (mc *mysqlConn) interpolateParams(query string, args []driver.Value) (string, error), which support time.Time but not support sql.NullTime, it simply convert sql.NullTime to String, and no error reported.

[jinger7281]

@jinger7281 Not related to the issue, but I recommend to use type TIMESTAMP instead of DATETIME for a column used to save a fixed point in time.

This is a historical problem, so, we must use DATETIME before we rewrite related logic

[dolmen]

From my experience, to avoid timezone problems with Go and MySQL I use the following settings:

• parseTime=true
• loc=UTC
• time_zone=+00:00

In the database I use only TIMESTAMP type, never DATETIME. This avoids problem on daylight saving switches.