Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In one Database table, created_at & updated_at are defined as:

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',

And in another database table, it's defined as:

`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',

created_at -> stores datetime when the data is created.
updated_at -> stores the datetime when data is updated.

Which definition among above two would you prefer & why?

share|improve this question

1 Answer 1

Tested on 5.5.34

I looked at this in detail (I'm interested for an upcoming project, and it's been a while since I've "got my hands dirty"...)

It appears that datetimes are hard to set defaults for - see here. From what I've read, things have got better in 5.6 - I'll see if I have time to test tomorrow.

I think that your best solution is as below. The answer to your question about the best way to CREATE your TABLE is shown (IMHO). It involves inversing the ON UPDATE clause, putting it (logically enough) with the updated_at field.

mysql> CREATE TABLE mt8
    -> (
    ->   billy int,
    ->   created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
    ->   updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At'
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> 
mysql> 
mysql> DELIMITER $$
mysql> create trigger my_ins8_trigger
    -> before insert on mt8
    -> for each row
    -> begin
    ->   set new.created_at = current_timestamp;
    -> end $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> 
mysql> 
mysql> insert into mt8 values(123, null, null);
Query OK, 1 row affected (0.08 sec)

mysql> insert into mt8 values(454, null, null);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mt8 values(1676, null, null);
Query OK, 1 row affected (0.06 sec)

mysql> insert into mt8 (billy) values(34334);
Query OK, 1 row affected (0.07 sec)

mysql> insert into mt8 (billy) values(3506);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mt8 (billy) values(3435454);
Query OK, 1 row affected (0.05 sec)

mysql> select * from mt8;
+---------+---------------------+---------------------+
| billy   | created_at          | updated_at          |
+---------+---------------------+---------------------+
|     123 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|     454 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|    1676 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|   34334 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
|    3506 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
| 3435454 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
+---------+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql> update mt8 set billy = 455 where billy = 454;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mt8;
+---------+---------------------+---------------------+
| billy   | created_at          | updated_at          |
+---------+---------------------+---------------------+
|     123 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|     455 | 2014-06-12 01:53:40 | 2014-06-12 01:54:26 |
|    1676 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|   34334 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
|    3506 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
| 3435454 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
+---------+---------------------+---------------------+
6 rows in set (0.00 sec)
mysql>

For the row with billy = 455, the update timestamp has changed, but not the created one - exactly as you'd want!

share|improve this answer
    
Mysql we have is 5.5.x. I would like to know if above DDL causes issue if we edit the data? –  MagePsycho Jun 11 at 14:34
    
A created_at trigger will just work for the first time you create the record. However, if you have an updated_at trigger, then yes, if you edit the data it will change the value. But of course you can edit the data - or is it the created_at field you wish to edit? Don't see a problem with doing that (editing created_at), but it kind of defeats the purpose of making your created_at trigger in the first place. –  Vérace Jun 11 at 14:38
    
I have updated my Question. Sorry for the confusion. –  MagePsycho Jun 11 at 14:44
    
Please check my updated Question. I need to select one among two definations. –  MagePsycho Jun 11 at 18:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.