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?

Rhodolite

Maybe change the type of LastLogin to sql.NullTime – Paroxysm

try definiting it as LastLogin *time.Time. – Tong

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

Hardpressed

How to assign nil value to LastLogin? – Cetane

@UysimTy I think this can be like user.LastLogin = sql.NullTime{} – Penicillin

Answer 2

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.

Anglim

All that's strictly necessary is gorm:"default:null" – Brenza

Answer 3

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

Serving

Hey thanks. I figure this is only meant for DB initialisation. In my case, since the DB is created without using gorm, my issue was just about storing null timestamp. So I guess this isn't the answer I was looking for, but thanks! One approach as some pointed out earlier is to represent the time.Time instance in Go using a pointer. – Rhodolite