I noticed that gorm sqlite will reserve timezone when it saves the field whose type is time.Time ,
but it ignores the timezone in where condition.
for example:
db.Create(&Record{ Time : time.Now() })
// my local timezone is "+8 Hours" ,assume that the value of Time is set to "2024-05-01 09:00:00+08:00"
var records []Record
db.Where("time >= ? , time.Date(2024,5,1,8,0,0,0,time.UTC) ).Find(&records)
In fact, "2024-05-01 09:00:00+08:00" is before than "2024-05-01 08:00:00 UTC" which is equal with "2024-05-01 16:00:00+08:00",
so records should not have the record that we created just now.
but gorm may ignore the timezone when compare the time field, so that records hold the record incorrectly.
This result is very counterintuitive.
I print the sql statement.
SELECT * FROM `records` WHERE time >= "2024-05-01 08:00:00"
gorm converted the time in condition to string "2024-05-01 08:00:00" without timezone, so sqlite just compared the string and got a wrong result.
I suggest that gorm should reserve timezone when it save and compare similarly ,or convert the field to UTC uniformly when it save and compare.
Description
I noticed that gorm sqlite will reserve timezone when it saves the field whose type is time.Time , but it ignores the timezone in where condition.
for example:
In fact, "2024-05-01 09:00:00+08:00" is before than "2024-05-01 08:00:00 UTC" which is equal with "2024-05-01 16:00:00+08:00", so records should not have the record that we created just now. but gorm may ignore the timezone when compare the time field, so that records hold the record incorrectly. This result is very counterintuitive.
I print the sql statement.
gorm converted the time in condition to string "2024-05-01 08:00:00" without timezone, so sqlite just compared the string and got a wrong result.
I suggest that gorm should reserve timezone when it save and compare similarly ,or convert the field to UTC uniformly when it save and compare.