Since version v2.8, if the time fields created_at, updated_at, and deleted_at are integer fields, the ORM component will automatically recognize and support them, writing second-level timestamp values.

Example SQL

This is the MySQL table structure used in the subsequent example code.

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `status` tinyint DEFAULT 0,
  `created_at` int(10) unsigned DEFAULT NULL,
  `updated_at` int(10) unsigned DEFAULT NULL,
  `deleted_at` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `user_detail` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `address` varchar(45) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

tip

If you try to test viewing the SQL statements executed by ORM operations, it’s recommended to enable debug mode (related documentation: ORM Senior - Debug Mode), the SQL statements will be automatically printed to log output.

created_at Writing Time

When executing the Insert/InsertIgnore/BatchInsert/BatchInsertIgnore methods, this time is automatically written. Subsequent update/delete operations will not change the content of the created_at field.

warning

It should be noted that the Replace method will also update this field because this operation is equivalent to deleting the existing old data and writing a new data record.

// INSERT INTO `user`(`name`,`created_at`,`updated_at`,`deleted_at`) VALUES('john',1731481488,1731481488,0)
g.Model("user").Data(g.Map{"name": "john"}).Insert()
// INSERT IGNORE INTO `user`(`id`,`name`,`created_at`,`updated_at`,`deleted_at`) VALUES(10000,'john',1731481518,1731481518,0) 
g.Model("user").Data(g.Map{"id": 10000, "name": "john"}).InsertIgnore()
// REPLACE INTO `user`(`id`,`name`,`created_at`,`updated_at`,`deleted_at`) VALUES(10000,'john',1731481747,1731481747,0)
g.Model("user").Data(g.Map{"id": 10000, "name": "john"}).Replace()
// INSERT INTO `user`(`id`,`name`,`created_at`,`updated_at`,`deleted_at`) VALUES(10001,'john',1731481766,1731481766,0) ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`updated_at`=VALUES(`updated_at`),`deleted_at`=VALUES(`deleted_at`)
g.Model("user").Data(g.Map{"id": 10001, "name": "john"}).Save()

deleted_at Soft Delete

When soft deletion is present (i.e., the deleted_at field exists), all query statements will automatically add conditions for deleted_at.

// UPDATE `user` SET `deleted_at`=1731481948 WHERE (`id`=10) AND `deleted_at`=0
g.Model("user").Where("id", 10).Delete()

Changes occur when querying:

// SELECT * FROM `user` WHERE (id>1) AND `deleted_at`=0
g.Model("user").Where("id>?", 1).All()

You can see that when the deleted_at field exists in the data table, all query operations involving the table automatically add the condition deleted_at=0.

updated_at Update Time

When executing the Save/Update methods, this time is automatically written. It should be noted that the Replace method will also update this field because it is equivalent to deleting the existing old data and writing a new data record.

info

If the deleted_at soft delete field also exists, the update operation statement will also contain the deleted_at condition.

// UPDATE `user` SET `name`='john guo',`updated_at`=1731481821 WHERE (`name`='john') AND `deleted_at`=0
g.Model("user").Data(g.Map{"name" : "john guo"}).Where("name", "john").Update()
// UPDATE `user` SET `status`=1,`updated_at`=1731481895 WHERE `deleted_at`=0 ORDER BY `id` ASC LIMIT 10
g.Model("user").Data("status", 1).OrderAsc("id").Limit(10).Update()
// INSERT INTO `user`(`id`,`name`,`created_at`,`updated_at`,`deleted_at`) VALUES(1,'john guo',1731481915,1731481915,0) ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`updated_at`=VALUES(`updated_at`),`deleted_at`=VALUES(`deleted_at`)
g.Model("user").Data(g.Map{"id": 1, "name": "john guo"}).Save()

Scenarios of Joint Table Queries

If several tables involved in an associated query have enabled the soft delete feature, the following situation will occur, where all relevant tables will have soft deletion time judgment added to the condition statements.

// SELECT * FROM `user` AS `u` LEFT JOIN `user_detail` AS `ud` ON (ud.id=u.id) WHERE (`u`.`id`=10) AND `u`.`deleted_at`=0 LIMIT 1
g.Model("user", "u").LeftJoin("user_detail", "ud", "ud.id=u.id").Where("u.id", 10).One()

Control the Granularity of Time Writing

The time field value writing in this chapter is by default in seconds-level timestamp. But if we want to control the granularity of time writing such as writing a millisecond-level timestamp, how do we do it? We can use Time Fields - SoftTimeOption.