MySQL timestamp fields - created/modified

Question

I have recently decided to stop using MySQL triggers for my created and modified date fields in my tables due to complications when running scripts included in the setup file on client deployment.

I have altered the fields this way: (example)

alter table users 
modify `created` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'

alter table users 
modify `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP 

When I now run my program and fill in a form to add data (form controls are databound), I get the message "Column 'created' does not allows nulls".

On already existing data, when I try to update, the modified date simply doesnt change.

I have looked at many articles such as Having both a Created and Last Updated timestamp columns in MySQL 4.0 but cannot find a solution.

How do I solve this?

I am using MySQL v 5.6.15.0

Answer 1

ALTER TABLE 'my_table'
CHANGE `created` TIMESTAMP DEFAULT 0,
CHANGE `modified` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

But be sure to give any value to created and modified during create and update operations.

Here is an example:

mysql> CREATE TABLE ts_test5 (
-> created TIMESTAMP DEFAULT 0,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts_test5 (created, data)
-> VALUES (NULL, ‘original_value’);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ts_test5;
+---------------------+---------------------+----------------+
| created             |updated              |data            |   
+---------------------+---------------------+----------------+
| 2005-01-04 14:47:39 | 0000-00-00 00:00:00 | original_value |
+---------------------+---------------------+----------------+
1 row in set (0.00 sec)

mysql> . . . time passes . . .

mysql> UPDATE ts_test5 SET data=’updated_value’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1
Changed: 1
Warnings: 0

mysql> SELECT * FROM ts_test5;
+---------------------+---------------------+---------------+
| created             |updated              |data           |
+---------------------+---------------------+---------------+
| 2005-01-04 14:47:39 | 2005-01-04 14:47:52 | updated_value |
+---------------------+---------------------+---------------+
1 row in set (0.00 sec)