sqlite数据库时区问题，engine.TZLocation与engine.DatabaseTZ设置的时机不同，时间打印结果会不同 #1612

New Issue

buexplain · 2020-03-20T12:28:10Z

buexplain commented

2020-03-20 12:28:10 +00:00

复现代码

package main

import (
	"encoding/json"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"os"
	"time"
	"xorm.io/xorm"
)

var engine *xorm.Engine

type Tag struct {
	ID int `xorm:"not null pk autoincr INTEGER"`
	CreatedAt time.Time `xorm:"DateTime created"`
	UpdatedAt time.Time `xorm:"DateTime updated"`
	Name         string `xorm:"unique TEXT"`
}

type List []*Tag

func init()  {
	var err error
    //sqlite3的时区设置为上海
	engine, err = xorm.NewEngine("sqlite3", "test.db?_loc=Asia/Shanghai")
	if err != nil {
		panic(err)
	}
    //本程序时区与数据库时区都设置为上海
    engine.TZLocation,_ = time.LoadLocation("Asia/Shanghai")
	engine.DatabaseTZ,_ = time.LoadLocation("Asia/Shanghai")

	err = engine.Sync2(new(Tag))
	if err != nil {
		panic(err)
	}
}

func main() {
	tag := new(Tag)
	tag.Name = time.Now().String()
	if _, err := engine.Insert(tag); err != nil {
		panic(err)
	}

	var result List
	err := engine.Find(&result)
	if err != nil {
		panic(err)
	}

	fmt.Println("通过结构体查询" ,result[0].CreatedAt)

	j, _ := json.Marshal(result)

	fmt.Println("json格式化结构体后", string(j))

	m, _ := engine.Query("select * from tag")
	fmt.Println("sql语句查询", string(m[0]["created_at"]))


	if engine.Close() == nil {
		_ = os.Remove("test.db")
	}
}

问题分析

时间的入库： xorm在插入时间的时候会进行格式化，此时xorm会丢失掉时区相关的信息，因为格式化的格式是：2006-01-02 15:04:05，不包含时区。

时间的出库： 当sqlite3拿到数据的时候会进行解析，因为xorm格式化的时候丢失了时区，此时sqlite3是按utc时区进行解析，然后sqlite3再根据打开时候的配置_loc的时区进行转换，这个时候时间就会产生了异常，然后xorm拿到的时间就是异常的，此时xorm也会判断sqlite3吐出的时区是否和配置的engine.DatabaseTZ相等，如果不相等也会进行转换。

#### 复现代码 ```golang package main import ( "encoding/json" "fmt" _ "github.com/mattn/go-sqlite3" "os" "time" "xorm.io/xorm" ) var engine *xorm.Engine type Tag struct { ID int `xorm:"not null pk autoincr INTEGER"` CreatedAt time.Time `xorm:"DateTime created"` UpdatedAt time.Time `xorm:"DateTime updated"` Name string `xorm:"unique TEXT"` } type List []*Tag func init() { var err error //sqlite3的时区设置为上海 engine, err = xorm.NewEngine("sqlite3", "test.db?_loc=Asia/Shanghai") if err != nil { panic(err) } //本程序时区与数据库时区都设置为上海 engine.TZLocation,_ = time.LoadLocation("Asia/Shanghai") engine.DatabaseTZ,_ = time.LoadLocation("Asia/Shanghai") err = engine.Sync2(new(Tag)) if err != nil { panic(err) } } func main() { tag := new(Tag) tag.Name = time.Now().String() if _, err := engine.Insert(tag); err != nil { panic(err) } var result List err := engine.Find(&result) if err != nil { panic(err) } fmt.Println("通过结构体查询" ,result[0].CreatedAt) j, _ := json.Marshal(result) fmt.Println("json格式化结构体后", string(j)) m, _ := engine.Query("select * from tag") fmt.Println("sql语句查询", string(m[0]["created_at"])) if engine.Close() == nil { _ = os.Remove("test.db") } } ``` #### 问题分析 **时间的入库：** xorm在插入时间的时候会进行格式化，此时xorm会丢失掉时区相关的信息，因为格式化的格式是：`2006-01-02 15:04:05`，不包含时区。 **时间的出库：** 当sqlite3拿到数据的时候会进行解析，因为xorm格式化的时候丢失了时区，此时sqlite3是按utc时区进行解析，然后sqlite3再根据打开时候的配置`_loc`的时区进行转换，这个时候时间就会产生了异常，然后xorm拿到的时间就是异常的，此时xorm也会判断sqlite3吐出的时区是否和配置的`engine.DatabaseTZ`相等，如果不相等也会进行转换。 1. [xorm的时间入库处理代码](https://gitea.com/xorm/xorm/src/branch/master/dialects/time.go#L21) 2. [sqlite3的时间出库处理代码](https://github.com/mattn/go-sqlite3/blob/master/sqlite3.go#L2093) 3. [xorm的时间出库处理代码](https://gitea.com/xorm/xorm/src/branch/master/session.go#L651)

lunny commented

2020-03-20 16:31:59 +00:00

Which version are you using? Once you set engine.DatabaseTZ, the previous data on database will not be changed with the new time one.

Which version are you using? Once you set `engine.DatabaseTZ`, the previous data on database will not be changed with the new time one.

buexplain commented

2020-03-21 08:18:14 +00:00

Which version are you using? Once you set engine.DatabaseTZ, the previous data on database will not be changed with the new time one.

版本：xorm.io/xorm v0.8.2
上面的例子，可以复现我的问题，数据每次都是新的，因为os.Remove("test.db")

> Which version are you using? Once you set `engine.DatabaseTZ`, the previous data on database will not be changed with the new time one. 版本：xorm.io/xorm v0.8.2 上面的例子，可以复现我的问题，数据每次都是新的，因为`os.Remove("test.db")`

buexplain closed this issue

2020-03-21 10:41:08 +00:00

buexplain reopened this issue

2020-03-23 08:04:43 +00:00

lunny commented

2021-06-12 08:52:38 +00:00

sqlite3 has a default DatabaseTZ as UTC, but since you override it. I think this should not an issue.

Could you help to confirm this is still a problem?

sqlite3 has a default DatabaseTZ as UTC, but since you override it. I think this should not an issue. Could you help to confirm this is still a problem?

lunny added the

need

feedback

label 2021-06-12 08:52:45 +00:00

lunny commented

2021-08-04 08:14:39 +00:00

I think this has been resolved.

lunny closed this issue

2021-08-04 08:14:40 +00:00

Sign in to join this conversation.