confusion between DateTime and TimeStamp in Mysql

Question

I am using mysql, and have four fields in all tables,

created_by varchar(128)
created_on timestamp default current_timestamp
updated_by varchar(128)
updated_on timestamp default current_timestamp

I have read on different answers on stack overflow that every time when some operation is performed on a table row, the field whose default value is current_timestamp is automatically updated. But I want to created_on to automatically insert current_timestamp when a new record updated, and when this record updated after some time, only updated_by field automatically updated to current_timestamp not the created_by.

[RTM](http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html), you can have only 1 field with `DEFAULT CURRENT_TIMESTAMP` prior to 5.6. Not sure why, but you'll either have to set the `updated` or `created` value manually, or create a trigger (which is, IMO, ugly). — Elias Van Ootegem, Feb 20 '15 at 13:01
may be this can help you http://stackoverflow.com/questions/4897002/mysql-current-timestamp-on-create-and-on-update — Vivek Vaghela, Feb 20 '15 at 13:03
possible duplicate of [Having both a Created and Last Updated timestamp columns in MySQL 4.0](http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0) — Elias Van Ootegem, Feb 20 '15 at 13:03

score 0 · Answer 1 · answered Feb 20 '15 at 12:58

0

created_by varchar(128)
created_on timestamp default current_timestamp
updated_by varchar(128)
updated_on timestamp default current_timestamp ON UPDATE CURRENT_TIMESTAMP

MORE INFO:: http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

answered Feb 20 '15 at 12:58

Alexis Peters

1,583
1
10
17

This will only work if OP is running mysql 5.6 or higher, though – Elias Van Ootegem Feb 20 '15 at 13:03
are you sure ? should be available since 5.0 – Alexis Peters Feb 20 '15 at 13:06
1

Check the [5.5 documentation](http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html): _"It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column"_. Voted to close this question as duplicate BTW(cf third comment below the question) – Elias Van Ootegem Feb 20 '15 at 13:13

ZZ-bb · Answer 2 · 2015-02-20T13:36:08.390

I use this structure:

created_by varchar(128)
created_on timestamp  NOT NULL DEFAULT '0000-00-00 00:00:00'
updated_by varchar(128)
updated_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

During first time INSERT INTO use NOW() for created_on. After that modifying the row will always automatically update the updated_on field. No need to touch the TIMESTAMP columns at all for UPDATE statements. (Note: The timestamp will be updated only if something is really updated. IE. Updating the columns with existing values doesn't modify anything so the timestamp is not updated.)

confusion between DateTime and TimeStamp in Mysql

2 Answers2