mysqlgotimetimestampgo-gorm

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


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?


Solution

  • 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