on update CURRENT_TIMESTAMP

User, date Message
Written by leerox
5 years ago
Category: General
5 posts since Thu, 06 Mar 08 		How do I go about removing the on update for my TIMESTAMP fields? When I create a TIMESTAMP field it always, be default has on update attached which I do not want.

Shouldn't this be part of the properties of the field?

Thanks
Written by kalvaro
5 years ago
441 posts since Thu, 29 Nov 07 		You need to specify CURRENT_TIMESTAMP as default value. If you don't want a default value then use 0.

This is the manual page that explains the feature:

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
Written by leerox
5 years ago
5 posts since Thu, 06 Mar 08 		So if I specify nothing it will default to on update CURRENT_TIMESTAMP?

I have always left it blank in the past which seems to auto change to CURRENT_TIMESTAMP.

If I try to specify this "CURRENT_TIMESTAMP" It still goes to on update when I export the Create SQL for each table.
Written by kalvaro
5 years ago
441 posts since Thu, 29 Nov 07

leerox wrote: If I try to specify this "CURRENT_TIMESTAMP" It still goes to on update when I export the Create SQL for each table.



Works for me (MySQL Server 4.1.13) with both HeidiSQL 3.20 and latest snapshot.

CREATE TABLE `foo` (
`id` int(10) unsigned NOT NULL auto_increment,
`bar` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

Do you get a different CREATE TABLE statement if you run this query?

SHOW CREATE TABLE your_table
Written by leerox
5 years ago
5 posts since Thu, 06 Mar 08 		Hi, I did that and it came up with this:

CREATE TABLE `products`
(
`PID` int(11) NOT NULL auto_increment,
`PCode` varchar(30) NOT NULL,
`PName` varchar(255) NOT NULL,
`PSDesc` text,
`PDesc` longtext NOT NULL,
`POrder` varchar(50) default '1',
`PImg` varchar(250) NOT NULL,
`PImgTN` varchar(250) NOT NULL,
`PImgWidth` varchar(50) default NULL,
`PImgHeight` varchar(50) default NULL,
`PWeight` double(6,3) NOT NULL,
`PPrice` double(11,4) NOT NULL,
`PTaxIsInclusive` varchar(3) NOT NULL default 'Yes',
`PTaxCode` varchar(10) NOT NULL default 'GST',
`PStock` double(12,3) NOT NULL default '0.000',
`PStockAlert` double(10,3) NOT NULL default '0.000',
`POnline` varchar(50) default 'No',
`PLatestRelease` varchar(3) NOT NULL default 'No',
`PHop` varchar(1) default 'N',
`PGrain` varchar(1) default 'N',
`OLDPID` varchar(10) NOT NULL,
`PDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`PMYOBChangeControl` varchar(10) NOT NULL,
`POnHold` varchar(1) NOT NULL default 'N',
`display_order` varchar(50) default '0000000001',
`display_order_online` varchar(1) default 'N',
`product_view_type` varchar(1) default 'P',
PRIMARY KEY (`PID`),
UNIQUE KEY `PID` (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



I also took a screenshot of the table properties in Heidi.
Written by Dr. Dave
5 years ago
95 posts since Sun, 27 Jan 08 		I just had the same issue, where I manually created a table and left the Default value blank. After executing a query, the Default value automatically changed itself to CURRENT_TIMESTAMP, and it went to NOT NULL.

All I did was re-open the table and cleared the Default and un-checked the not null box so it would accept null.

Once I did this, I re-executed a few more queries and it didn't go back to a default of CURRENT_TIMESTAMP.

I have no idea why I had to tell it again to do what I wanted, but it 'seemed' to work.

Now, I of course have an alternate solution, which works perfectly for only timestamping when you want. Instead of making it a TIMESTAMP field, make it a VARCHAR field with the default length of 50. Presto. For example:


CREATE TABLE /*!32312 IF NOT EXISTS*/ `mytable` (
`capturedDate` varchar(50) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO myTable (capturedDate) VALUES (CURRENT_TIMESTAMP);



And there you have it. CURRENT_TIMESTAMP used in a VARCHAR field is a wonderful thing. :)
 

Please login to leave a reply, or register at first.