mysql - How to tell gorm to save missing time.Time fields as NULL and not '0000-00-00'?

Question

I have this custom type for users:

type User struct {
    UserID    uint64 `gorm:"primaryKey"`
    CreatedAt time.Time
    UpdatedAt time.Time
    LastLogin time.Time
}

When passing to gorm's db.Create() method, a user is initialised as follows:

return User{
    UserID:    userID,
    AccountID: accountID,
    UserType:  userType,
    Status:    status,
    UserInfo:  &userInfo,
    CreatedAt: now,
    UpdatedAt: now,
}

Because LastLogin is a nullable timestamp column in MySQL, I didn't initialise its value here.

Now gorm will parse the unset value to '0000-00-00 00:00:00.000000' in the SQL statement, and cause the following error.

Error 2021-01-29 15:36:13,388 v1(7) error_logger.go:14 192.168.10.100 - - default - 0 Error 1292: Incorrect datetime value: '0000-00-00' for column 'last_login' at row 1

While I understand how MySQL doesn't allow zero timestamp values without having to change some modes, I can easily initialise the time.Time field to be some faraway dates, e.g. 2038-ish. How do I tell gorm to just pass the zero Time field as NULL in the SQML instead?

question from:https://stackoverflow.com/questions/65951219/how-to-tell-gorm-to-save-missing-time-time-fields-as-null-and-not-0000-00-00

Answer 1

So you have a couple of options here. You can make LastLogin a pointer which will mean it can be a nil value:

type User struct {
    ID        uint64 `gorm:"primaryKey"`
    CreatedAt time.Time
    LastLogin *time.Time
}

Or like @aureliar mentioned you can use the sql.NullTime type

type User struct {
    ID        uint64 `gorm:"primaryKey"`
    CreatedAt time.Time
    LastLogin sql.NullTime
}

Now when you create that object in the DB and do not set LastLogin it will save as NULL in the DB.

https://gorm.io/docs/models.html

It's worth noting, if you use sql.NullTime, in the struct you will see a default timestamp rather than a nil value