Gorm: db.Create() do not touch updated_at field

[imbaMF]

Why updated_at field was updated in db.Create()?
Must be used only created_at field! We just created new item!! not updated!!
updated_at must be any default 0000-01-01 or nil (Updated_at *time.Time)
So when the item is Created() updated_at field is nil or default, then we can use updated_at field to view not moderated rows (new data) something like SELECT * FROM table WHERE updated_at IS NULL (or WHERE updated_at is 0000-01-01 (default)), but now i need to create a new field moderated and use it. Plix improve it!!!

[zaneli]

You can customize "gorm:update_time_stamp" Callback.
https://github.com/jinzhu/gorm/blob/836fb2c19d84dac7b0272958dfb9af7cf0d0ade4/callback_create.go#L31-L48

type TestTable struct {
    ID        uint `gorm:"primary_key"`
    Name      string
    CreatedAt time.Time
    UpdatedAt *time.Time
    DeletedAt *time.Time `sql:"index"`
}
db.DropTable(&TestTable{})
db.CreateTable(&TestTable{})
db.Callback().Create().Replace("gorm:update_time_stamp", func(scope *gorm.Scope) {
    if !scope.HasError() {
        now := gorm.NowFunc()
        if createdAtField, ok := scope.FieldByName("CreatedAt"); ok {
            if createdAtField.IsBlank {
                createdAtField.Set(now)
            }
        }
    }
})
if err := db.Create(&TestTable{Name: "test"}).Error; err != nil {
    return err
}
return nil

INSERT  INTO `test_tables` (`name`,`created_at`,`updated_at`,`deleted_at`) VALUES ('test','2019-07-08 16:06:42',NULL,NULL)

[franklingu]

You can customize "gorm:update_time_stamp" Callback.
https://github.com/jinzhu/gorm/blob/836fb2c19d84dac7b0272958dfb9af7cf0d0ade4/callback_create.go#L31-L48

type TestTable struct {
  ID        uint `gorm:"primary_key"`
  Name      string
  CreatedAt time.Time
  UpdatedAt *time.Time
  DeletedAt *time.Time `sql:"index"`
}
db.DropTable(&TestTable{})
db.CreateTable(&TestTable{})
db.Callback().Create().Replace("gorm:update_time_stamp", func(scope *gorm.Scope) {
  if !scope.HasError() {
      now := gorm.NowFunc()
      if createdAtField, ok := scope.FieldByName("CreatedAt"); ok {
          if createdAtField.IsBlank {
              createdAtField.Set(now)
          }
      }
  }
})
if err := db.Create(&TestTable{Name: "test"}).Error; err != nil {
  return err
}
return nil

INSERT  INTO `test_tables` (`name`,`created_at`,`updated_at`,`deleted_at`) VALUES ('test','2019-07-08 16:06:42',NULL,NULL)

Thanks a lot for the example.

I have the problem that even if I removed the 'gorm:update_time_stamp' for Create(), if I use time.Time instead of *time.Time for CreatedAt and UpdatedAt, those two fields will be inserted as '0000-00-00 00:00:00'. Is it possible to disable behaviour?

[franklingu]

this is what I used, not sure if this is recommended or not for setting time.Time to NULL when create/save a new model

func updateTimeStampForCreateCallback(scope *gorm.Scope) {
    if !scope.HasError() {
        if createdAtField, ok := scope.FieldByName("CreatedAt"); ok {
            if createdAtField.IsBlank {
                createdAtField.Set(nil)
            }
        }

        if updatedAtField, ok := scope.FieldByName("UpdatedAt"); ok {
            if updatedAtField.IsBlank {
                updatedAtField.Set(nil)
            }
        }
    }
}

[zaneli]

func main() {
    databaseURL := fmt.Sprintf(
        "%s:%s@tcp(%s:%s)/%s?charset=utf8mb4",
        "root", "password", "127.0.0.1", "3306", "zaneli",
    )
    if err := create(databaseURL); err != nil {
        panic(err)
    }
}

func create(url string) error {
    db, err := gorm.Open("mysql", url)
    if err != nil {
        return err
    }
    db.LogMode(true)

    if err := db.AutoMigrate(&User{}).Error; err != nil {
        return err
    }

    // before remove callback
    if err := db.Create(&User{Name: "test1"}).Error; err != nil {
        return err
    }

    db.Callback().Create().Remove("gorm:update_time_stamp")

    // after remove callback
    if err := db.Create(&User{Name: "test2"}).Error; err != nil {
        return err
    }

    return nil
}

type User struct {
    ID        int
    Name      string
    CreatedAt *time.Time
    UpdatedAt *time.Time
}

[2020-01-17 16:51:33]  [2.27ms]  INSERT INTO `users` (`name`,`created_at`,`updated_at`) VALUES ('test1','2020-01-17 16:51:33','2020-01-17 16:51:33')

[2020-01-17 16:51:33]  [1.20ms]  INSERT INTO `users` (`name`,`created_at`,`updated_at`) VALUES ('test2',NULL,NULL)

mysql> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from users;
+----+-------+---------------------+---------------------+
| id | name  | created_at          | updated_at          |
+----+-------+---------------------+---------------------+
|  1 | test1 | 2020-01-17 07:51:33 | 2020-01-17 07:51:33 |
|  2 | test2 | NULL                | NULL                |
+----+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

I Could not be reproduced, it seems works fine for me.
Please provide a complete runnable program to reproduce your issue.
Do you use latest gorm version?

[franklingu]

@zaneli thank you for the quick response.

I think for the program you provided, this is very important
~go
type User struct {
ID int
Name string
CreatedAt *time.Time
UpdatedAt *time.Time
}
~

But this requires CreatedAt and UpdatedAt to be *time.Time. and that is somehow inconvenient for using.

so with this
~go
type User struct {
ID int
Name string
CreatedAt time.Time
UpdatedAt time.Time
}
~

then we will have
~sql
INSERT INTO users (name,created_at,updated_at) VALUES ('test2','0000-00-00 00:00:00','0000-00-00 00:00:00')
~

[zaneli]

created_at and updated_at have not null constraint?
So, which value do you want to set when create it?

[franklingu]

created_at and updated_at have not null constraint?
So, which value do you want to set when create it?

actually I am not using auto migrate. instead I am using created_at datetime default CURRENT_TIMESTAMP, updated_at datetime default CURRENT_TIMESTAMP on change CURRENT_TIMESTAMP

so when reading, I am sure there should be values. when writing, I would like db to do created_at and updated_at :)

[zaneli]

Umm...
For example, define other struct for only use when create.

func create(db *gorm.DB, u *User) error {
    if err := db.Create(u.mkUserWithoutTime()).Error; err != nil {
        return err
    }

    return nil
}

type User struct {
    ID        int
    Name      string
    CreatedAt time.Time
    UpdatedAt time.Time
}

type userWithoutTime struct {
    ID   int
    Name string
}

func (userWithoutTime) TableName() string {
    return "users"
}

func (u *User) mkUserWithoutTime() *userWithoutTime {
    return &userWithoutTime{Name: u.Name}
}

[2020-01-17 17:54:10]  [1.78ms]  INSERT INTO `users` (`name`) VALUES ('test3')

[franklingu]

@zaneli Thanks for the suggestion. That is one way to fix it.

What I want: gorm do not do anything to CreatedAt and UpdatedAt when creating/saving with time.Time{}. Kind of like UnsetColumn is what I need--then I can check IsZero and Unset in callbacks.

What I am thinking:

1. your solution of shadow entity shall work. more code.
2. use *time.Time. Then reading becomes a bit weird with "entity.CreatedAt != nil &&" pattern
3. use mysql.NullTime (same thing as *time.Time)

What I thought of but mostly not going to work:

1. clone "createCallback" and substitute with custom implementation(this is the core part. and very risky. plus some functions are package level only)
2. do "createdAtField=nil" in my custom callback. did not work (I still do not fully understand this part. worth thinking about it)

What I am thinking the problem is: it is quite like readonly field problem. I want CreatedAt and UpdatedAt not be to inserted/updated by gorm but gorm should read it.
Or another direction: there should be some way for me to unset a column in callbacks.

[franklingu]

@zaneli Thanks for the suggestion. That is one way to fix it.

What I want: gorm do not do anything to CreatedAt and UpdatedAt when creating/saving with time.Time{}. Kind of like UnsetColumn is what I need--then I can check IsZero and Unset in callbacks.

What I am thinking:

1. your solution of shadow entity shall work. more code.
2. use *time.Time. Then reading becomes a bit weird with "entity.CreatedAt != nil &&" pattern
3. use mysql.NullTime (same thing as *time.Time)

What I thought of but mostly not going to work:

1. clone "createCallback" and substitute with custom implementation(this is the core part. and very risky. plus some functions are package level only)
2. do "createdAtField=nil" in my custom callback. did not work (I still do not fully understand this part. worth thinking about it)

What I am thinking the problem is: it is quite like readonly field problem. I want CreatedAt and UpdatedAt not be to inserted/updated by gorm but gorm should read it.
Or another direction: there should be some way for me to unset a column in callbacks.

just found a workable solution. Hacky :)

~~~go
package main

import (
"fmt"
"time"

"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"

)

func main() {
databaseURL := fmt.Sprintf(
"%s:%s@tcp(%s:%s)/%s?charset=utf8mb4",
"root", "", "127.0.0.1", "3306", "test_db",
)
if err := create(databaseURL); err != nil {
fmt.Printf("%v\n", err)
panic(err)
}
}

func create(url string) error {
db, err := gorm.Open("mysql", url)
if err != nil {
return err
}
db.LogMode(true)

db.Callback().Create().Replace("gorm:update_time_stamp", updateTimeStampForCreateCallback)

// after remove callback
if err := db.Create(&User{Name: "test2"}).Error; err != nil {
    return err
}

return nil

}

type User struct {
ID int
Name string
CreatedAt time.Time
UpdatedAt time.Time
}

func updateTimeStampForCreateCallback(scope *gorm.Scope) {
if !scope.HasError() {
if createdAtField, ok := scope.FieldByName("CreatedAt"); ok {
if createdAtField.IsBlank {
createdAtField.IsIgnored = true
}
}

    if updatedAtField, ok := scope.FieldByName("UpdatedAt"); ok {
        if updatedAtField.IsBlank {
            updatedAtField.IsIgnored = true
        }
    }
}

}

~~~

[franklingu]

@zaneli Thanks for the suggestion. That is one way to fix it.

What I want: gorm do not do anything to CreatedAt and UpdatedAt when creating/saving with time.Time{}. Kind of like UnsetColumn is what I need--then I can check IsZero and Unset in callbacks.

What I am thinking:

1. your solution of shadow entity shall work. more code.
2. use *time.Time. Then reading becomes a bit weird with "entity.CreatedAt != nil &&" pattern
3. use mysql.NullTime (same thing as *time.Time)

What I thought of but mostly not going to work:

1. clone "createCallback" and substitute with custom implementation(this is the core part. and very risky. plus some functions are package level only)
2. do "createdAtField=nil" in my custom callback. did not work (I still do not fully understand this part. worth thinking about it)

What I am thinking the problem is: it is quite like readonly field problem. I want CreatedAt and UpdatedAt not be to inserted/updated by gorm but gorm should read it.
Or another direction: there should be some way for me to unset a column in callbacks.

Or kind of register Omit in callback

[zaneli]

@franklingu
I think you can use sql:"DEFAULT" tag.

func create(db *gorm.DB) error {
    db.Callback().Create().Remove("gorm:update_time_stamp")

    u := &User{Name: "test"}
    if err := db.Create(u).Error; err != nil {
        return err
    }
    fmt.Printf("INSERTED: %+v\n", u)
    return nil
}

type User struct {
    ID        int
    Name      string
    CreatedAt time.Time `sql:"DEFAULT: CURRENT_TIMESTAMP"`
    UpdatedAt time.Time `sql:"DEFAULT: CURRENT_TIMESTAMP"`
}

[2020-01-22 10:39:46]  [1.60ms]  INSERT INTO `users` (`name`) VALUES ('test')
[1 rows affected or returned ]

[2020-01-22 10:39:46]  [1.66ms]  SELECT `created_at`, `updated_at` FROM `users`  WHERE (id = 1)
[1 rows affected or returned ]

INSERTED: &{ID:1 Name:test CreatedAt:2020-01-22 01:39:46 +0000 UTC UpdatedAt:2020-01-22 01:39:46 +0000 UTC}

mysql> desc users;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| name       | varchar(255) | NO   |     | NULL              |                |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

mysql> select * from users;
+----+------+---------------------+---------------------+
| id | name | created_at          | updated_at          |
+----+------+---------------------+---------------------+
|  1 | test | 2020-01-22 01:39:46 | 2020-01-22 01:39:46 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

[franklingu]

@zaneli this does work. I need to check why this works though. LOL.

Question, since we are talking about sql struct tag now, can we do "ON UPDATE CURRENT_TIMESTAMP" with sql tag(gorm does not have this tag)

[franklingu]

@zaneli

Thank you very much for all the help so far. Let me try to summarize:
~go
type User struct {
ID int
Name string
CreatedAt time.Time sql:"DEFAULT: CURRENT_TIMESTAMP"
UpdatedAt time.Time sql:"DEFAULT: CURRENT_TIMESTAMP"
}
~
This will prevent gorm from having "CreatedAt" and "UpdatedAt" columns because the value is blank and the field has default value--this only works for db.Create though. (Kind of similar to the magic replace of the callback by setting the field to be Ignored)
db.Update do not update "UpdatedAt" if the value is blank
If use db.Save for updating, blank "UpdatedAt" will be updated (By design)

Did I understand anything wrong? Thank you

[github-actions[bot]]

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog