os/gtime: TimeZone is Error when using NewFromTime function in gdb

[weimx2023]

Go version

go version go1.22.5 windows/amd64

GoFrame version

v2.7.2

Can this bug be reproduced with the latest release?

Option Yes

What did you do?

using NewFromTimeStamp function is right

What did you see happen?

the TimeZone is error

What did you expect to see?

fix

[weimx2023]

when using query between start time and end time TimeZone of NewFromTime is also error

[wln32]

@weimx2023 请检查数据库数据是否正确，2024-08-06 00:00:00和2024-08-05 16:00:00 是sql显示问题，勿惊
我复现了你的代码，数据库插入的数据是一致的

startTime, _ := time.Parse(time.RFC3339, "2024-08-05T16:00:00Z")

	gStartTime := gtime.NewFromTime(startTime)
	gStartTimestamp := gtime.NewFromTimeStamp(startTime.UnixMilli())
	fmt.Println("from time", gStartTime)
	fmt.Println("from time stamp", gStartTimestamp)
	g.
		DB().
		Model("test_time").
		Safe().
		Insert(g.Map{
			"my_time":      gStartTime,
			"my_timestamp": gStartTimestamp,
		})

mysql连接配置 mysql:root:123456@tcp(127.0.0.1:3306)/test?loc=Local&parseTime=true
表结构
CREATE TABLE `test_time` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `my_timestamp` timestamp NULL DEFAULT NULL,
  `my_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

@weimx2023 Please check whether the database data is correct, 2024-08-06 00:00:00 and 2024-08-05 16:00:00 are SQL display problems, don’t be alarmed
I reproduced your code and the data inserted into the database is consistent.

startTime, _ := time.Parse(time.RFC3339, "2024-08-05T16:00:00Z")

gStartTime := gtime.NewFromTime(startTime)
gStartTimestamp := gtime.NewFromTimeStamp(startTime.UnixMilli())
fmt.Println("from time", gStartTime)
fmt.Println("from time stamp", gStartTimestamp)
g.
DB().
Model("test_time").
Safe().
Insert(g.Map{
"my_time": gStartTime,
"my_timestamp": gStartTimestamp,
})

mysql connection configuration mysql:root:123456@tcp(127.0.0.1:3306)/test?loc=Local&parseTime=true
table structure
CREATE TABLE `test_time` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `my_timestamp` timestamp NULL DEFAULT NULL,
  `my_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

[weimx2023]

Hi there，I tried it and the time in the database is indeed correct, but this displayed log might cause confusion during debugging. If I directly copy and paste the output SQL statement, the execution result might be different.

[wln32]

@gqcn 目前初步定位到问题在gdb_func.go:920行，格式化sql时，对time.Time类型没有使用带有时区信息的格式化

修改time格式化后的效果如下， @weimx2023 以下效果是否满意

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

@gqcn At present, the problem has been initially located in the gdb_func.go:920 line. When formatting sql, the time.Time type is not formatted with time zone information.

The effect after modifying time formatting is as follows. @weimx2023 Are you satisfied with the following effect?

[gqcn]

@weimx2023 @wln32 其实这个也算是老生常态的问题了，程序的时区和数据库时区不一致，只要时区统一了，不是问题。程序在打印时间的时候不会做任何时区转换，那么主要就是时间对象的时间处理了。建议程序：

• 统一使用一种时区处理时间对象转换。
• 传递给数据库的时间对象统一时区。

若有其他问题欢迎继续提issue。

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

@weimx2023 @wln32 In fact, this is a common problem. The time zone of the program is inconsistent with the time zone of the database. As long as the time zones are unified, it is not a problem. The program will not do any time zone conversion when printing the time, so the main thing is the time processing of the time object. Recommended procedure:

• Uniformly use one time zone to handle time object conversion.
• The time object passed to the database has a unified time zone.

[weimx2023]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

@gqcn At present, the problem has been initially located in the gdb_func.go:920 line. When formatting sql, the time.Time type is not formatted with time zone information.

The effect after modifying time formatting is as follows. @weimx2023 Are you satisfied with the following effect?

@wln32 @gqcn Adding time zones to time is indeed a good idea, but I think it can be clearly explained in the notes. This way, users won't have any confusion when using it.

[wln32]

@weimx2023 @wln32 其实这个也算是老生常态的问题了，程序的时区和数据库时区不一致，只要时区统一了，不是问题。程序在打印时间的时候不会做任何时区转换，那么主要就是时间对象的时间处理了。建议程序：

• 统一使用一种时区处理时间对象转换。
• 传递给数据库的时间对象统一时区。

若有其他问题欢迎继续提issue。

不太认同，打印sql语句应当原样输出时间，时区也是时间的一部分，而不是忽略掉，标准库的time类型String方法是带有时区信息的
不带时区的输出默认时间是本地时区，但对于一些项目来说，sql语句不带时区，在出现问题时，可能对于调试有困难

[Issues-translate-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

---

@weimx2023 @wln32 In fact, this is a common problem. The time zone of the program is inconsistent with the time zone of the database. As long as the time zones are unified, it is not a problem. The program will not do any time zone conversion when printing the time, so the main thing is the time processing of the time object. Recommended procedure:

• Uniformly use one time zone to handle time object conversion.
• The time object passed to the database unifies the time zone.

If you have other questions, please continue to raise an issue.

I don’t quite agree. When printing SQL statements, the time should be output as it is. The time zone is also part of the time, rather than ignored. The time type String method of the standard library has time zone information.
The default time for output without time zone is the local time zone. However, for some projects, SQL statements do not have time zone. When problems occur, it may be difficult to debug.