Page 1 of 1

DELETE clause deleting stuff it shouldn't: timestamp related

Posted: Sun Oct 16, 2005 7:59 pm
by Ambush Commander
I am running these two queries:

Code: Select all

INSERT INTO `twp_captchas` ( `id`, `value`, `creation` ) VALUES (15,'cliojabi',1129510256);
DELETE FROM `twp_captchas` WHERE creation < 1129499456;
The net result is that there are no rows in the table, which doesn't seem right, as I'm fairly certain 510256 > 499456 and thus doesn't fulfill the condition and isn't deleted...

The table looks like:

Code: Select all

CREATE TABLE `twp_captchas` (
  `id` int(11) NOT NULL default '0',
  `value` varchar(8) collate utf8_unicode_ci NOT NULL default '',
  `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `value` (`value`,`creation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Posted: Sun Oct 16, 2005 8:08 pm
by Ambush Commander
Okay, that's the problem. I omitted the cleanup function and inspected the rows, and lo and behold, the timestamp is set at 0000-00-00 00:00:00. Hmm... that's strange...

Posted: Sun Oct 16, 2005 8:13 pm
by Ambush Commander
Temporarily fixed the problem by changing the column from TIMESTAMP to INT... can anyone tell me why TIMESTAMP was rejecting the output of time()?

Posted: Sun Oct 16, 2005 8:19 pm
by feyd
timestamp format isn't unix_timestamp ;)

fyi, a timestamp field is updated automatically when the record is updated ;)

Posted: Sun Oct 16, 2005 8:23 pm
by Ambush Commander
Ohhh... so if I need to store a unix timestamp, do I use INT?

Posted: Sun Oct 16, 2005 8:34 pm
by feyd
INT(10) UNSIGNED

yes.