DELETE clause deleting stuff it shouldn't: timestamp related

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

DELETE clause deleting stuff it shouldn't: timestamp related

Post 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;
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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...
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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()?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

timestamp format isn't unix_timestamp ;)

fyi, a timestamp field is updated automatically when the record is updated ;)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Ohhh... so if I need to store a unix timestamp, do I use INT?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

INT(10) UNSIGNED

yes.
Post Reply