[selowitch]

I'd like to create a timestamp column called last_modified in MySQL that automatically updates to the current date and time whenever a) a row is created or b) a row is modified. But although I have created a field with the TIMESTAMP attribute, it does not work as expected. Any ideas? The MySQL documentation is very difficult to understand, so please don't tell me to RTFM.

[geraldartman]

It would help to have some more to go on.

Are you adding a row? What happens?
When you modify a row's value? What happens?

What's the table definition?

[selowitch]

Originally Posted by geraldartman

It would help to have some more to go on.
Are you adding a row? What happens?
When you modify a row's value? What happens?

Well, when I add or modify a row, I can of course update the DATETIME field (which I've named last_modified) by inserting the NOW() function into it, either with a raw MySQL command, through phpMyAdmin, or via Navicat (although in the latter case, not through Navicat's GUI elements per se but via a raw MySQL command). But I'd like this to happen automatically without that extra step.

Originally Posted by geraldartman

What's the table definition?

By that I think you mean, "If I were to recreate the identical table from scratch, what would the SQL command be to do it"? Well, the answer is I'm not sure: Is there any easy way to see the table definition, or do I have to reverse-compose the syntax?

BTW, FWIW, I'm running MySQL - 4.1.13a and phpMyAdmin - 2.8.0.4.

[geraldartman]

A datetime field is not a timestamp.
The timestamp field always changes to current server values on insert/update.
Datetime is 'user' settable to a value you pass it.

Example, I use a datetime to show last record update for a user and set it each admin session modifying the user values. I use timestamp for last access. Every time a user logs in, I update a login count field and the when gets added automatically in the timestamp. See the difference.

Check your table definitions and post if more questions.

[selowitch]

Originally Posted by geraldartman

A datetime field is not a timestamp.

Technically speaking, no. But that is its practical application for me.

Originally Posted by geraldartman

The timestamp field always changes to current server values on insert/update.

Well, I agree that it should, but it does not appear to be working.

Originally Posted by geraldartman

Datetime is 'user' settable to a value you pass it.

Right, but you see I don't want the hassle I would experience if I create a record using, say, Navicat, and then have to figure out the record_id of the row I just created, then write a SQL command with the NOW() attribute in it. I'd rather this happened automatically so I don't have to think about it.

Example, I use a datetime to show last record update for a user and set it each admin session modifying the user values. I use timestamp for last access. Every time a user logs in, I update a login count field and the when gets added automatically in the timestamp. See the difference?

Yes, thank you. That's a nice clear explanation.

Check your table definitions and post if more questions.

Please respond to my earlier question about how to retrieve the table definition from an existing table.

[geraldartman]

look up describe and show tables in the manual