CODEWITHSUNDEEP
mysqlgotimetimestampgo-gorm
Eric L.
Eric L.

Reputation: 237

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?

Upvotes: 12

Views: 17506

Answers (3)

Milad ABC
Milad ABC

Reputation: 376

You can use DEFAULT NULL in your gorm tag like this:

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

In this case gorm would not insert a zero value of 0000-00-00 00:00:00 for LastLogin.

Upvotes: 2

Vusal Shahbazov
Vusal Shahbazov

Reputation: 151

Use like this

type Test struct {
    ID uint64 `gorm:"primary_key" json:"id"`
    CompletedAt sql.NullTime `gorm:"type:TIMESTAMP NULL"`
}

It helps to make CompletedAt field nullable timestamp type

Upvotes: 0

Chris Townsend
Chris Townsend

Reputation: 2716

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

Upvotes: 21

Related Questions