Golang inserting time.Now into database being shifted to UTC

Question

I am trying to insert a time.Now() field into the database, but what keeps happening is the time is shifted forward to UTC. I understand the idea is to always to that then convert to local time when presenting to the user. Problem is I have inherited this system and it is currently far to ingrained to be easily changed.

Any tips? I saw that you can set the loc in the DSN but it does not explain what to change it to nor its actual effect, so I would appreciate information there.

Edit: Some information, it is MySQL DB,using go-mysql 1.1 and go 1.6.3. The data is being inserted into a DATETIME field.

The data is incorrect at the insert. GORM Debug shows one time and MySQL query logger shows it shifted forward.

I have found that the issue is that the go-mysql driver automatically shifts times to be UTC, and this can be altered using the loc parameter in the DSN. This however also changes how the time is returned.

To resolve this add loc=Local to your DSN.

Ketene

What is the database? What's the data type of the column receiving the date? Can you show the code you're using when presenting to the user? – Aggappe

To echo @Aggappe - It sounds like you recognize the database field needs to stay UTC, so you'll need to adjust on insert and select statements instead. This is a very common problem amongst most any dev (converting datetime values to local representation). – Mansuetude

Check out #25318654 this answer. – Shandy

Answer 1

This is the way I use to always store local time. Works fine even if your VPS is located on another timezone country:

db, err = gorm.Open("mysql", "root:@tcp(localhost:3306)/mydatabase?charset=utf8&parseTime=True&loc=America%2FSao_Paulo")

Squab

Answer 2

try this

query := "UPDATE ABC SET updatedAt = CONVERT_TZ(?, '+00:00', @@session.time_zone) WHERE id = ?"

Crissman

Answer 3

because time in golang parse will use the UTC

Geese