Can't edit any object : Error 1292: Incorrect datetime value: '0000-00-00' for column 'created_at' at row 1

[kedare]

Hello.

When trying to edit any gorm object, I am getting this error :

Error 1292: Incorrect datetime value: '0000-00-00' for column 'created_at' at row 1

There is an example of GORM model and the related configuration :

// PortalAnnouncement represents an announcement that can be shown on the portal frontpage
type PortalAnnouncement struct {
	gorm.Model
	Title           string
	Content         string
	Importance      string
	Shown           bool
	Date            time.Time
	Always          bool
	BackgroundColor string
	TextColor       string
}

// SetupAdmin configure the administration backend and returns it
func SetupAdmin(DB *gorm.DB) (*admin.Admin, error) {
	adminInstance := admin.New(&admin.AdminConfig{DB: DB})
	adminInstance.AddResource(&database.PortalCategory{})
	adminInstance.AddResource(&database.PortalService{})
	adminInstance.AddResource(&database.PortalLink{})
	adminInstance.AddResource(&database.AuthenticationLog{})
	adminInstance.AddResource(&database.PortalAnnouncement{})

	return adminInstance, nil

}

Any idea of how can I fix this ? This field is part of the fields automatically generated by GORM through the gorm.Model, there is the failing query:

INFO[0024] UPDATE `portal_announcements` SET `created_at` = ?, `updated_at` = ?, `deleted_at` = ?, `title` = ?, `content` = ?, `importance` = ?, `shown` = ?, `date` = ?, `always` = ?, `background_color` = ?, `text_color` = ?  WHERE `portal_announcements`.`deleted_at` IS NULL AND `portal_announcements`.`id` = ?  module=gorm type=sql

Thanks

[fshiori]

I think it is the same problem with https://github.com/jinzhu/gorm/issues/595

[lastzero]

So GORM doesn't work with MySQL >= 5.7?

[fshiori]

No, GORM can work with MySQL 5.7 , but must disable the NO_ZERO_DATE in your sql mode.

[lastzero]

Just tested GORM and didn't see this issue (date should in fact not be 0000-00-00, probably only happens if you set it like that manually). Note that you must add ?parseTime=true to the database DSN for date parsing to work, otherwise you'll see a similar error.

[sijad]

I have same issue with ?parseTime=true

❯ mysql -V
mysql  Ver 8.0.13 for osx10.14 on x86_64 (Homebrew)

[DannyHinshaw]

What is the proper solution to this? I'm having issues with MySQL 8, GORM is not autogenerating fields that it should be (CreatedAt, UpdatedAt, DeletedAt). Instead it throws an error when I insert records: Error 1292: Incorrect datetime value: '0000-00-00' for column 'created_at' at row 1

My base model:

type Base struct {
	ID        uuid.UUID  `gorm:"primary_key" json:"id"`
	CreatedAt time.Time  `json:"created_at"`
	UpdatedAt time.Time  `gorm:"index" json:"updated_at"`
	DeletedAt *time.Time `gorm:"index" json:"deleted_at"`
}

My connection url/string I'm using:

MYSQL_CONNECT_STRING: "user:password@tcp(mysql)/db?charset=utf8&parseTime=True&loc=Local"

[gmsgowtham]

I've also had the same issue with timestamp fields. Try adding default: NOW() to the timestamp struct definition

i.e:

CreatedAt  time.Time `gorm:"column:created_at; type:timestamp; default: NOW(); not null" json:"created_at"

[raselcse10]

I tried but no luck. Getting the same error.

[cityiron]

I get the same error too.

[nikolaigut]

I get the same error, also with *time.Time.

[nguyennk92]

I found a workaround by wrapping your object inside an array. i.e:

a = model{
    key: "primaryKey",
    fieldA: "fieldA",
    fieldB: "fieldB",
}
db.Save(a)

This will resulted in SQL:

UPDATE `model` SET fieldA = "fieldA", fieldB = "fieldB", updated_at = "...", created_at = "0000..." 
WHERE key="primaryKey" 

and will fail with error as created_at is 0000-00-00. Do this instead:

...
db.Save([]model{a})

This will produce SQL:

INSERT INTO `model` (`key`,`fieldA`,`fieldB`,`updated_at`,`created_at`) 
VALUES (...) 
ON DUPLICATE KEY UPDATE `fieldA` = VALUES(`fieldA`), `updated_at` = VALUES(`updated_at`)

[jovanygonzalez]

No veo ninguna solución aquí.