CODEWITHSUNDEEP
mysql
Kinyanjui Kamau
Kinyanjui Kamau

Reputation: 1936

MySQL timestamp fields - created/modified

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

Upvotes: 7

Views: 12661

Answers (3)

Deepak Rai
Deepak Rai

Reputation: 2203

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)

Upvotes: 5

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

Change 

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

To: 

alter table users modify 
   `created` TIMESTAMP NOT NULL DEFAULT now()

Or:

alter table users modify 
   `created` TIMESTAMP NOT NULL DEFAULT current_timestamp

Upvotes: 3

ffflabs
ffflabs

Reputation: 17481

Depending on the driver, the 0000-00-00 date might be treated as null, which isn't valid for your column definition. But if that wasn't the case, any date prior to 1970 isn't a valid timestamp either.

I'd try defining "created" as DATETIME instead of TIMESTAMP 

alter table users modify `created` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00'

(That is the lowest date a DATETIME can take). As to why the modified column is not working, it's part of the same problem. Tables are supposed to have only one timestamp column, but if you put two or more, only the first one can have CURRENT_TIMESTAMP. Silly mysql limitations.

This restriction will be lifted in future version, but I guess yours hasn't that yet.

Upvotes: 0

Related Questions