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

Deepak Rai · Answer 1 · 2020-12-17T09:38:42.403

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)

Thanks for your reply. I am using datasets to add/update my data, no made queries. E.g this.usersTableAdapter.Update(this.payrollDataSet.users); this.payrollDataSet.users.AcceptChanges(); — Kinyanjui Kamau, Mar 04 '14 at 12:34

score 3 · Answer 2 · answered Mar 04 '14 at 11:44

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

score 0 · Accepted Answer · answered Mar 04 '14 at 11:41

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.

Thanks for your reply. The version I am using is the latest for Visual Studio development. I have altered the created field as you stated. Unfortunately, I am still experiencing the same problems. Could it be the c# datasets I am using? — Kinyanjui Kamau, Mar 04 '14 at 12:29

MySQL timestamp fields - created/modified

3 Answers3