#1023 Preserve timestamp field with ON UPDATE CURRENT_TIMESTAMP
http://sourceforge.net/forum/forum.php?thread_id=1723419&forum_id=72909 describes this, but I'll copy that information here:
=========================
This is for phpmyadmin 2.10.0.2.
I have a timestamp field, set for the NOW() function, in a database record. I manually update the records in this database, and the intent of the field is for, most times anyway, the timestamp field to log the last time a record was updated.
However, on some occasions when I update a field, I DON'T want the timestamp data in the record to change; I want it to remain whatever it had been.
I'd think that if I just used the pulldown menu under FUNCTION to change NOW to the blank entry, the timestamp would remain the same. But it doesn't. It updates to the current date/time. But if I copy the previous date/time, paste it back into the timestamp field after the field has been unwantedly updated, again change FUNCTION from NOW to blank, and save, I am able to reset the timestamp field back to what it was.
This is crazy. All I want is a way, using phpmyadmin, to, if I choose to, update some fields in a record, but keep the timestamped field holding the last value it had and not updating.
What am I doing wrong?
Below is the structure of the table I am talking about. It's the Last_Update field that I'd like to sometimes keep static when updating other fields.
LastName varchar(25) latin1_swedish_ci Yes NULL Browse distinct values Change Drop Primary Unique Index Fulltext
FirstName varchar(25) latin1_swedish_ci Yes NULL Browse distinct values Change Drop Primary Unique Index Fulltext
preferred varchar(25) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext
egreet varchar(25) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext
don't_mail_to char(1) latin1_swedish_ci Yes x Browse distinct values Change Drop Primary Unique Index Fulltext
UsedCheck char(1) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext
Street varchar(40) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext
City varchar(25) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext
State char(2) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext
Zip varchar(5) latin1_swedish_ci No Browse distinct values Change Drop Primary Unique Index Fulltext
Last_Update timestamp ON UPDATE CURRENT_TIMESTAMP No CURRENT_TIMESTAMP Browse distinct values Change Drop Primary Unique Index Fulltext
_2007 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_2006 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_2005 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_2004 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_2003 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_2002 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_2001 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_2000 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
_1999 int(6) Yes 0 Browse distinct values Change Drop Primary Unique Index Fulltext
Source varchar(15) latin1_swedish_ci No OACS Browse distinct values Change Drop Primary Unique Index Fulltext
SourcePerson varchar(12) latin1_swedish_ci No Dave Browse distinct values Change Drop Primary Unique Index Fulltext
email varchar(50) latin1_swedish_ci No
Logged In: YES
user_id=210714
Originator: NO
Please read
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html
As I understand it, this is the intended behavior of ON UPDATE CURRENT TIMESTAMP.
Logged In: YES
user_id=1383652
Originator: NO
>As I understand it, this is the intended behavior of ON UPDATE CURRENT
>TIMESTAMP.
Marc,
if you deliberately change "NOW" to "" (empty) you should expect that the old value will be kept - IMHO.
In my own scripts "ON UPDATE CURRENT TIMESTAMP" takes only effect if i
*don't* send a value for that field. But in pma edit there *is* a value set (from the old state of the row) and should only be overriden if Function is "NOW".
Otherwise we have to use pma 2.9 where it works like semphatis (and me too ;) wishes. Either take this as a bug report or a feature request.
YMMV
YMMV
Logged In: YES
user_id=210714
Originator: NO
OK
I noticed this bug too after switching servers and upgrading phpmyadmin to the latest version...
The problem I see is even if you select the blank (first) choice in the drop-down, the unchanged time is not included in the SQL statement to set timestamp = (time you wanted)... If you change the time then it will set it to that...
This is extremely frustrating for me as I need to update like 50 records manually, and now with each one I have to edit it twice! (once to change the field I needed to fix, a second time to fix the date).
I tried the suggested fix using the following code in my config, which removed the NOW() from the default, however the issue still remains that it will not update the timestamp if the existing time is the same as the time you want to update with...
$cfg['DefaultFunctions'] = array(
'FUNC_CHAR' => '',
'FUNC_DATE' => '',
'FUNC_NUMBER' => '',
'first_timestamp' => ''
);
I noticed this bug too after switching servers and upgrading phpmyadmin to the latest version...
The problem I see is even if you select the blank (first) choice in the drop-down, the unchanged time is not included in the SQL statement to set timestamp = (time you wanted)... If you change the time then it will set it to that...
This is extremely frustrating for me as I need to update like 50 records manually, and now with each one I have to edit it twice! (once to change the field I needed to fix, a second time to fix the date).
I tried the suggested fix using the following code in my config, which removed the NOW() from the default, however the issue still remains that it will not update the timestamp if the existing time is the same as the time you want to update with...
$cfg['DefaultFunctions'] = array(
'FUNC_CHAR' => '',
'FUNC_DATE' => '',
'FUNC_NUMBER' => '',
'first_timestamp' => ''
);
In version 3.1.1 (at least in my tests) there is no default function NOW() displayed. If the user does not change the value of the TIMESTAMP, the current value is not sent and the timestamp is updated. IMO this is the expected behavior for a column with ON UPDATE CURRENT TIMESTAMP.
To handle the case of someone wanting to alter the "normal" behavior, there could be a checkbox "Keep current value" next to this timestamp's value.
Moved to feature requests.