The time zone generated by CURRENT_TIMESTAMP is not the expected time zone

[wencan]

os: fedora 38 linux: 6.5 go: 1.20 github.com/dolthub/go-mysql-server v0.17.0 github.com/go-sql-driver/mysql v1.7.1 github.com/jmoiron/sqlx v1.3.5

system timezone: +08:00

code:

package main

import (
    "fmt"
    "time"

    sqle "github.com/dolthub/go-mysql-server"
    "github.com/dolthub/go-mysql-server/memory"
    "github.com/dolthub/go-mysql-server/server"
    "github.com/dolthub/go-mysql-server/sql"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
    "github.com/wencan/fastrest/restutils"
)

var (
    dbName  = "mydb"
    address = "localhost"
    port    = 3306
)

type ProviderBase struct {
    // ID id
    ID int64 `json:"id" db:"id"`

    // Name 名称
    Name string `json:"name" db:"title"`

    // Remark 备注
    Remark string `json:"remark" db:"remark"`

    // Deleted 逻辑删除标志。0为未删除。1为已删除
    Deleted bool `json:"deleted" db:"deleted"`

    // CreateTime 创建时间
    CreateTime time.Time `json:"create_time" db:"create_time"`

    // UpdateTime 更新时间
    UpdateTime time.Time `json:"update_time" db:"update_time"`
}

func main() {
    ctx := sql.NewEmptyContext()

    db := memory.NewDatabase(dbName)
    db.EnablePrimaryKeyIndexes()
    provider := memory.NewDBProvider(db)
    engine := sqle.NewDefault(provider)

    config := server.Config{
        Protocol: "tcp",
        Address:  fmt.Sprintf("%s:%d", address, port),
    }
    s, err := server.NewDefaultServer(config, engine)
    if err != nil {
        panic(err)
    }
    go func() {
        err = s.Start()
        if err != nil {
            panic(err)
        }
    }()
    defer s.Close()

    dbx, err := sqlx.Open("mysql", "tcp(localhost:3306)/mydb?parseTime=true&loc=Asia%2FShanghai")
    if err != nil {
        panic(err)
    }
    defer dbx.Close()

    var now time.Time
    err = dbx.GetContext(ctx, &now, `SELECT NOW()`)
    if err != nil {
        panic(err)
    }
    fmt.Println(now)

    _, err = dbx.ExecContext(ctx, `CREATE TABLE provider_base (
        id bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
        title varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
        remark varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '备注',
        deleted tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志。0为未删除。1为已删除',
        create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (id)
      ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='供应商';`,
    )
    if err != nil {
        panic(err)
    }

    _, err = dbx.ExecContext(ctx, `INSERT INTO provider_base (title, remark) VALUES (?, ?)`, "第一行", "第一行的备注")
    if err != nil {
        panic(err)
    }

    var providers []*ProviderBase
    err = dbx.SelectContext(
        ctx,
        &providers,
        `SELECT id, title, remark, deleted, create_time, update_time FROM provider_base`,
    )
    if err != nil {
        panic(err)
    }

    fmt.Println(restutils.JsonString(providers))
}

output:

2023-10-28 10:23:46.426343 +0800 CST
[{"id":1,"name":"第一行","remark":"第一行的备注","deleted":false,"create_time":"2023-10-28T02:23:46+08:00","update_time":"2023-10-28T02:23:46+08:00"}]

expected:

2023-10-28 10:23:46.426343 +0800 CST
[{"id":1,"name":"第一行","remark":"第一行的备注","deleted":false,"create_time":"2023-10-28T10:23:46+08:00","update_time":"2023-10-28T10:23:46+08:00"}]

[okhowang]

according to https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_unix-timestamp

When the date argument is a [TIMESTAMP](https://dev.mysql.com/doc/refman/8.4/en/datetime.html) column, 
[UNIX_TIMESTAMP()](https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_unix-timestamp)
returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.

it looks like a bug in go-mysql-server's unix_timestamp

BTW from_unixtime has same problem according to https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_from-unixtime it's should return date in session timezone

[okhowang]

I notice TIMESTAMP and DATE/DATETIME/TIME were stored in time.Time it may work unexpected because they all have no timezone info. but they are processed with timezone in time.Time