Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
wencan opened this issue Oct 28, 2023 · 2 comments
Open

Comments

@wencan
Copy link

wencan commented Oct 28, 2023

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
Copy link
Contributor

okhowang commented Jun 18, 2024

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
Copy link
Contributor

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants