Golang Sql-MySQL - 日期/日期时间 0001 年

Question

我正在使用 go-sql-driver/mysql 库从 MySQL 实例中提取数据，但遇到了一个问题，即任何 DATE/DATETIME 都没有正确扫描字段，尤其是月份和日期是正确的，但 YEAR YEAR 和 TIME 始终为 0001，时间始终为 00:00:00。

所有其他字段都被正确拉取。有问题的 2 个字段是 date_added (DATE) 和 date_timestamp (DATETIME)

如果有任何帮助，我将不胜感激

示例:

0001-01-01 00:00:00 +0000 UTC

我添加了 parseTime=true 连接参数也没有成功。

连接字符串:

mysql, err := sql.Open("mysql", "username:password@tcp(10.37.42.1:3306)/mydb?parseTime=true")

查询:

var (
    id              int
    result_id       int
    status          string
    product         string
    serial          string
    station         string
    stage           string
    operator        string
    operator_name   string
    failstep        string
    sequence_rev    int
    date_added      time.Time
    date_timestamp  time.Time
    stage_order     int
    is_retest       int
    extra1          string
    extra2          string
    extra3          string
    extra4          string
    extra5          string
    workorder       string
    segment         string
    retest_reason   string
    wo_qty          int
)

queryString := `
    SELECT d.id, d.result_id, status.status AS status, parent.product AS product, parent.serial AS serial, station.station AS station, stage.description AS stage, u.username AS operator, CONCAT(u.first_name, ' ', u.last_name) AS operator_name, d.failstep, 
    d.sequence_rev, d.date_added, d.date_timestamp, d.stage_order, d.is_retest, 
    d.extra1, d.extra2, d.extra3, d.extra4, d.extra5, d.workorder, parent.segment, d.retest_reason, parent.wo_qty 
    FROM test_result_detail d 
    LEFT JOIN test_result parent ON d.result_id=parent.id
    LEFT JOIN test_result_status status ON d.status_id=status.id
    LEFT JOIN seq_test_stage stage ON d.stage_id=stage.id
    LEFT JOIN seq_test_set station ON d.station_id=station.id
    LEFT JOIN auth_user u ON d.operator_id=u.id
    WHERE d.id > ? and d.date_added >= '2016-01-01'
    LIMIT 5`

    rows, err := mysql.Query(queryString, LastDetailResult.LastID)
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }


    // iterate through the rows
    for rows.Next() {
        fmt.Println(rows.Columns())
        err := rows.Scan(&id, &result_id, &status, &product, &serial, &station, &stage, &operator, &operator_name, &failstep, &sequence_rev, &date_added, &date_timestamp, &stage_order, &is_retest, &extra1, &extra2, &extra3, &extra4, &extra5, &workorder, &segment, &retest_reason, &wo_qty)
        if err != nil {
            //fmt.Println(err)
        }

        // find parent record in Mongo
        var Parent ResultDetail
        var emptyParent ResultDetail
        Session.DB("opt").C("serials").Find(bson.M{"serial": serial}).One(&Parent)

        // if parent cannot be found omit the insert
        if Parent == emptyParent {
            fmt.Println("NO PARENT RECORD")
            continue
        }

        // check mongo to see if this is a duplicate record, if so omit
        var Duplicates []ResultDetail
        Session.DB("opt").C("serial_history").Find(bson.M{"parent": Parent.ID}).All(&Duplicates)

        // TODO check for first_pass vs retry
        if Duplicates != nil {
            // is this a detail record, if so omit
            // if not a duplicate but is an additional pass then flag FirstPass = false
            //fmt.Println(Duplicates)
            fmt.Println("Found Duplicates")
        }

        // detail record data cleanup

        // insert into detailed test result collection
        // d := Session.DB("opt").C("serial_history")
        // i := bson.NewObjectId()
        //insertError := d.Insert(&ResultDetail{ID: i, Operator: operator, Station: station, Serial: serial, Extra1: extra1, Extra2: extra2, Extra3: extra3, Extra4: extra4, Extra5: extra5, LegacyID: id, FirstPass: true, Workorder: workorder, Status: status, Product: product, Parent: Parent.ID, FailStep: failstep, WoQTY: wo_qty, DateTimestamp: date_timestamp, Segment: segment, Stage: stage, OperatorName: operator_name, Order: stage_order, Date: date_added})


        fmt.Println(date_timestamp)

    }

Answer 1

我弄清楚发生了什么，我的 var 声明 sequence_rev 中的字段出错了:

error: sql: Scan error on column index 10: converting driver.Value type ("") to a int: invalid syntax.

这是因为该字段不是必需的并且可以为空。我将我的 var 声明从 int 更新为 *int，这解决了下游的日期问题。

sequence_rev *int