Gorm How to set a timestamp attribute just like updated_at / created_at

Your Question

Hi, Thanks for the awesome library. In the following example, I would like notifications_last_checked_at act as the same as updated_at / created_at

CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `notifications_last_checked_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

type User struct {
    Id                                             uint           `gorm:"primary_key" json:"id"`
    NotificationsLastCheckedAt time.Time      `json:"notifications_last_checked_at"`
    CreatedAt                               time.Time      `json:"created_at"`
    UpdatedAt                              time.Time      `json:"updated_at"`
}

although in this situation, an error will be raised as below

Error 1292: Incorrect datetime value: '0000-00-00' for column 'notifications_last_checked_at' at row 1
INSERT INTO `users` (`notifications_last_checked_at`,`created_at`,`updated_at`) VALUES ('0000-00-00 00:00:00','2021-08-15 07:36:38.105','2021-08-15 07:36:38.105')

Is it possible to reflect the DEFAULT CURRENT_TIMESTAMP on notificatiosn_last_checked_at just like updated_at and created_at ? 🤔

The document you expected this should be explained

Expected answer

Comment From: s-takehana

How about autoCreateTime/autoUpdateTime tag?

https://gorm.io/docs/models.html#Creating-Updating-Time-Unix-Milli-Nano-Seconds-Tracking

Comment From: s-takehana

package main

import (
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "time"
)

type User struct {
    Id                         uint      `gorm:"primary_key" json:"id"`
    Name                       string    `json:"name"`
    NotificationsLastCheckedAt time.Time `gorm:"autoCreateTime" json:"notifications_last_checked_at"`
    CreatedAt                  time.Time `json:"created_at"`
    UpdatedAt                  time.Time `json:"updated_at"`
}

func main() {
    dsn := "root:*****@tcp(mysql:3306)/gorm"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})

    db.AutoMigrate(&User{})

    db.Debug().Create(&User{Name: "User1"})
}

2021/08/19 00:39:56 /workspace/main.go:23
[19.544ms] [rows:1] INSERT INTO `users` (`name`,`notifications_last_checked_at`,`created_at`,`updated_at`) VALUES ('User1','2021-08-19 00:39:56.191','2021-08-19 00:39:56.191','2021-08-19 00:39:56.191')

mysql> describe users;
+-------------------------------+-----------------+------+-----+---------+----------------+
| Field                         | Type            | Null | Key | Default | Extra          |
+-------------------------------+-----------------+------+-----+---------+----------------+
| id                            | bigint unsigned | NO   | PRI | NULL    | auto_increment |
| name                          | longtext        | YES  |     | NULL    |                |
| notifications_last_checked_at | datetime(3)     | YES  |     | NULL    |                |
| created_at                    | datetime(3)     | YES  |     | NULL    |                |
| updated_at                    | datetime(3)     | YES  |     | NULL    |                |
+-------------------------------+-----------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Comment From: take

ooooo thanks! I think this will work. 👯

Comment From: ghost

@s-takehana Hi I'm using gorm to retrieve data from Mysql DB, and the date type stored in DB is in the following format: 2022-03-11T04:58:50+01:00, so I want to get the date in timestamp (ex: 2354534234242) format, so please are there any configurations I have to follow to get what I want?

Comment From: s-takehana

@BM-Nadir Like this?

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "time"
)

type User struct {
    gorm.Model
    Name     string
    Birthday time.Time
}

func main() {
    dsn := "root:*****@tcp(mysql:3306)/gorm"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})

    db.AutoMigrate(&User{})

    db.Debug().Create(&User{Name: "User1", Birthday: time.Now()})

    var s string

    db.Debug().Model(&User{}).Select("UNIX_TIMESTAMP(birthday)").Scan(&s)
    fmt.Println(s)
}

2022/07/12 08:03:24 /workspace/main.go:22
[14.753ms] [rows:1] INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`birthday`) VALUES ('2022-07-12 08:03:24.361','2022-07-12 08:03:24.361',NULL,'User1','2022-07-12 08:03:24.359')

2022/07/12 08:03:24 /workspace/main.go:26
[1.496ms] [rows:1] SELECT UNIX_TIMESTAMP(birthday) FROM `users` WHERE `users`.`deleted_at` IS NULL
1657613004.360

https://gorm.io/docs/advanced_query.html#Pluck

Comment From: ghost

@s-takehana brother here is an example of what I'm working on: test-go-date

Comment From: s-takehana

@BM-Nadir Is this all right?

package main

import (
    "fmt"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type User struct {
    Id        int    `json:"id" gorm:"column:Id"`
    Name      string `json:"name" gorm:"column:NAME"`
    CreatedOn int    `json:"createdOn" gorm:"column:CREATEDON;autoCreateTime"`
    UpdatedOn int    `json:"updatedOn" gorm:"column:UPDATEDON;autoUpdateTime"`
}

func main() {
    dsn := "root:*****@tcp(mysql:3306)/gorm"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})

    db.AutoMigrate(&User{})

    db.Debug().Create(&User{Name: "User1"})

    var user User

    db.Debug().Find(&user)
    fmt.Printf("%+v\n", user)
}

2022/07/13 03:12:15 /workspace/main.go:22
[15.858ms] [rows:1] INSERT INTO `users` (`Id`,`NAME`,`CREATEDON`,`UPDATEDON`) VALUES (0,'User1',1657681935,1657681935)

2022/07/13 03:12:15 /workspace/main.go:26
[1.020ms] [rows:1] SELECT * FROM `users`
{Id:0 Name:User1 CreatedOn:1657681935 UpdatedOn:1657681935}

https://gorm.io/docs/models.html#Creating-x2F-Updating-Time-x2F-Unix-Milli-x2F-Nano-Seconds-Tracking

If you prefer to save UNIX (milli/nano) seconds instead of time, you can simply change the field’s data type from time.Time to int

Query Hooks may also help.

https://gorm.io/docs/advanced_query.html#Query-Hooks

Comment From: ghost

@s-takehana the solution you gave me works on creating a new object but doesn't work if the table already exists with a timestamp date, so thanks and I want to share with you that I reached what I wanted using hooks " AfterFind() " and then reset the result to seconds using ".Unix()"

I have another question about "embedded" tag field: in the docs, it says that the embedded struct will insert its fields into the parent struct like:

`type Author struct { Name string Email string }

type Blog struct { ID int Author Author gorm:"embedded" Upvotes int32 } // equals type Blog struct { ID int64 Name string Email string Upvotes int32 }

// -- -- but when I tried it I got this:

type Blog struct { ID int64 Author: { Name string Email string } Upvotes int32 }` // -- -- how can I reach the same as the docs?

Comment From: s-takehana

@BM-Nadir This test case may be helpful. https://github.com/go-gorm/gorm/blob/4d40e34734289137d9ca8fc2b69bf8de98a7448c/tests/embedded_struct_test.go#L13-L84