Gorm Save time.Time type data(local time) with pg timestamp(without timezone) type，fetched data is not correct(force marked as UTC)

Your Question

when insert time.Time（with client timezone） data into pg timestamp(without timezone) column，then fetch it，get wrong data with UTC timezone

The document you expected this should be explained

I'm using gorm v2 with postgresql

go.mod
gorm.io/driver/postgres v1.1.2
gorm.io/gorm v1.21.16

and in application, I use timestamp (without timezone) to store data, like created_at,updated_at ... the problme is: when fetch records from table, timestamp columns data force UTC.

example: a 'users' table, insert a record in '2021-11-01 15:04:05'(client timezone Aisa/Shanghai), then fetch record, it become "2021-11-01 15:04:05 +0000 UTC"

same question like this https://github.com/jackc/pgx/issues/924

env:

client:timezone Asia/Shanghai
dsn: "host=xxxx user=xxx password=xxx dbname=xxx search_path=xxxx port=xxx sslmode=disable TimeZone=Asia/Shanghai"
db:
SELECT current_setting('TIMEZONE')
------
Asia/Shanghai

type User struct {
    ID        int       `json:"id" gorm:"primarykey"`
    Name      string    `json:"name" gorm:"column:name"`
    CreatedAt time.Time `json:"created_at" gorm:"column:created_at"`
    UpdatedAt time.Time `json:"updaed_at" gorm:"column:updated_at"`
}
...
db.First(&user)
...
fmt.Println(user.CreatedAt)

actual: 2021-11-01 15:04:05 +0000 UTC
expect: 2021-11-01 15:04:05 +0800 CST (Asia/Shanghai)

Expected answer

how to deal with this situation，any best practise？

Comment From: mkyc

Can you try if providing NowFunc helps?

db, err := gorm.Open(connection, &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
    NowFunc: func() time.Time {
        ti, _ := time.LoadLocation("Asia/Shanghai")
        return time.Now().In(ti)
    },
})

I used it in some other case and it helped me at least in tests.

Comment From: jackwootton

@longlihale Why did you close this issue?

Comment From: ghost

@jackwootton :astonished: sorry,because I saw that there was a good answer and no reply in a couple of days, so I thought this issue had been resolved :relieved:. sorry re open it～

Comment From: forwaard

@mkyc sorry the late reply to your original comment,and thank you for your prompt reply,but it doesn't work for me. I used both

https://github.com/lib/pg

and 

https://github.com/jackc/pgx

write the demo, when use 'timestamptz' type store data then fetch, everything is ok,but when use 'timestamp' store same thing, they all get the wrong data(with the timezone issue)

I think because Golang's time.Time type has timezone info, when scan 'timestamp' type data, it's not clear to use which timezone, so set UTC for default.

with issues (pg) https://github.com/lib/pq/issues/329 and (pgx) https://github.com/jackc/pgx/issues/924

The project I'm working on is a Rails/Java Refactoring project, in Rails or Java(Date) situation, framework set the 'timestamp' type data with client application's timezone. I think in Golang there maybe has same behavior,but it's not.

Now I use a helper method in every model's AfterFind hook

func ForceCorrectTime(t time.Time) time.Time {
    loc, _ := time.LoadLocation("Asia/Shanghai")
    return time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), loc)
}

....

func (a *User) AfterFind(tx *gorm.DB) (err error) {
    a.CreatedAt = util.ForceCorrectTime(a.CreatedAt)
    a.UpdatedAt = util.ForceCorrectTime(a.UpdatedAt)
    return
}

it's ugly... :sweat_smile:

Maybe there's a better way? :muscle:

Comment From: mkyc

@FORWAARD we struggle with similar thing in our project. Consider storing timestamp without timezone:

db, err := gorm.Open(connection, &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
    NowFunc: func() time.Time {
        utc, _ := time.LoadLocation("")
        return time.Now().In(utc)
    },
})

Depends how timezone is understood in context of specific data you might then want to: - use user timezone (stored in user profile) to cast time into his timezone - use this data timezone stored in separate column to cast time into its timezone

Both ways AfterFind sounds like good option because you can customise it depends on data. Usually there are two options: - you want to show to user when something was done according to where that user is located now (or usually). - you want to show to user when something was done according to where that activity took place

So you'd use timezone from previously mentioned places accordingly.

Also please consider, that depends on frontend technology, you might even return UTC time directly to client and then do timezone manipulations on client side before rendering it.

Just to support that view you can have look at this comment (which for our internal discussion was found by @jackwootton).

Anyways hope you'll be able to figure it out - timezones are PITA 🙈

Comment From: forwaard

@mkyc thx, very good solution :thumbsup:. I'll try in my next project ☺️

Comment From: rhuanpk

I know this topic is already closed but... I really had a hard time figuring out how this was all working and finding a nice solution.

Finally, looking at the GORM documentation I saw that it had this link where we can pass the loc parameter in the connection string to set its default timezone.

Anyway, I hope this can still be useful to someone.

Comment From: bernardolm

postgres

Hi @rhuanpk! The issue was opened with a question about postgres timezone and the solution in your commented links is about a MySQL solution. :)

Comment From: rhuanpk

Hi @bernardolm!

Ok that was a pretty embarrassing mistake, sorry. LOL!

But as I already said, I hope this helped someone, even if it was just an idea of how to proceed. :)