Date-stamping a change on a specific column ?

[ramasaig]

My database table has a timestamp column which will show when a record has been changed. Is it possible to have a timestamp linked to a specific column ?

One of the columns in my table defines whether a record should be displayed at all (i.e. included in any search). I'm looking to record a date when that value has been changed. I'm likely to make that change in phpMyAdmin rather than by using a query (at least initially). I could enter a date in another column manually, but I'm likely to forget.

[danNL]

maybe this will help
http://bugs.mysql.com/bug.php?id=27645#c254993

[ramasaig]

Thanks. It's interesting, and it would lead me to think the answer to my question is no. However, that thread's more about the merits/demerits of timestamp, responding to any update (of any column). I wanted something specific to one column that wouldn't change if other columns were updated. I'd be quite happy with a timestamp.

There's not really a problem if I update with a query, as I can insert the current date as a string in any column I want. It's just while using phpMyAdmin that I'll have to remember to do it.

[danNL]

Wouldn't this take care of what you're looking for?

`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

[ramasaig]

Thanks. Yes, if I was doing it with a query I could add a clause like that whenever I changed the 'display' column, and insert the date in another column. But not when using 'phpMyAdmin', as I can't put a function in a column (as per the link you gave in your first reply).

[r937]

Is it possible to have a timestamp linked to a specific column ?

no

[danNL]

Hmm, maybe I'm not fully understanding your question. This is how I currently understand it:

1. You want a column that will update with the current date when any field in that row has been updated?

Here's a quick example that works for me via a query or change in phpMyAdmin:

CREATE TABLE IF NOT EXISTS `emails` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `email` varchar(100) default NULL,
  `updated_at` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

[r937]

Hmm, maybe I'm not fully understanding your question. This is how I currently understand it:

1. You want a column that will update with the current date when any field in that row has been updated?

no, only when a specific column has been updated

[ramasaig]

Thanks guys. I think we've arrived at a definitive answer, which I suspected all along. The documentation rarely tells you what you CAN'T do, so it might have taken ages to reach this conclusion without the forum.