Post 4.1.X TIMESTAMP columns, Changes to the timestamp column

anthony522

Could somebody explain to me what exactly is going on with timestamp columns now in the post 4.1.X world? My host recently updated their MySQL to 4.1.10-nt and it seems my timestamp columns have all gone to poop.

I tried reading up in the documentation but couldn't quite make out what I was trying to do.

Basically, I would like to make a table that has a timestamp column that updates itself with the timestamp when the particular row was added or modified.

Any help and any further explanation would be GREATLY appreciated.

devinemke

Originally posted by anthony522
Basically, I would like to make a table that has a timestamp column that updates itself with the timestamp when the particular row was added or modified.

that is precisely the whole point of the TIMESTAMP filed type. the main difference as of mySQL 4.1.x is that timstamp columns are now stored in the regular datetime format (YYYY-MM-DD HH:MM:SS). you should read up on this and other changes here.

Sxooter

Note that if the db is set to run in maxdb mode, a timestamp is just a datetime type...

anthony522

Originally posted by devinemke
that is precisely the whole point of the TIMESTAMP filed type. the main difference as of mySQL 4.1.x is that timstamp columns are now stored in the regular datetime format (YYYY-MM-DD HH:MM:SS). you should read up on this and other changes here.

I did read that but I was asking for more of a clarification. I can't get the timestamp fields to work anymore since this upgrade.

For example if I do this to create a table:

CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP);

Then I insert a row into that table and then try to query that table to see what the timestamp it is for the row, it displays as null.

Sxooter

What's the exact query you're using ot feed the database to insert the row?

anthony522

I think I just figured out that the DBA setup something incorrectly, wooops heh.

He's fixing it now because on my test server with 4.1.10 installed the column is behaving as expected.

Just cost me almost a full day to figure that out....grrRRRrrrrrr

Thank you everybody for your attention.