I did some real digging in the bug list for event scheduler issues.
It seems that a certain time calculation for the event scheduler was not portable. This bug was fixed as of MySQL 5.1.48 (Fixed June 2, 2010).
There was a past issue about SHOW EVENTS not getting events from the correct database. This bug was fixed as of MySQL 5.1.57 (Fixed May 5, 2011).
The latest scheduler bug was fixed July 5, 2011 in MySQL 5.1.58
You are using MySQL 5.1.41. You may want to upgrade to the latest version of MySQL 5.1 which is 5.1.58. No scheduler bugs exists as of today.
CAVEAT
On another note, I would change the SQL query to not only do less work
Instead of your DELETE query:
delete from ta_table where timestampdiff(minute,ti_time,now())>120;
Restructure it as follows:
delete from ta_table where ti_time < (now() - interval 2 hour);
Your DELETE will calculate against every row in the table. This new DELETE stops short when comparing ti_time against a time value (now() - interval 2 hour) instead of computing timestampdiff on every row.
Make sure ti_time is indexed. If not, do this:
ALTER TABLE ta_table ADD INDEX (ti_time);
Assuming the table is MyISAM, you may also want to periodically shrink the table every month like this:
ALTER TABLE ta_table ENGINE=MyISAM;
I hope this information helps !!!
UPDATE 2011-07-19 08:00 EDT
From the last chat room session lovesh and I had, here is the example I ran to create the event on my PC running MySQL 5.5.12:
drop database lovesh;
create database lovesh;
use lovesh
create table mydata (id int not null auto_increment primary key,ti_time timestamp DEFAULT current_timestamp) ENGINE=MyISAM;
DELIMITER $$
DROP PROCEDURE IF EXISTS `lovesh`.`LoadMyData` $$
CREATE PROCEDURE `lovesh`.`LoadMyData` ()
BEGIN
DECLARE NDX INT;
SET NDX = 0;
WHILE NDX < 100 DO
INSERT INTO mydata (ti_time) VALUES (NOW() - INTERVAL CEILING(14400*RAND()) SECOND);
SET NDX = NDX + 1;
END WHILE;
END $$
DELIMITER ;
show create table mydata\G
SHOW CREATE PROCEDURE LoadMyData\G
CALL lovesh.LoadMyData();
CREATE TABLE ta_table LIKE mydata;
ALTER TABLE ta_table DISABLE KEYS;
INSERT INTO ta_table SELECT SQL_NO_CACHE * FROM mydata;
ALTER TABLE ta_table ENABLE KEYS;
CREATE EVENT ev
ON SCHEDULE
EVERY 1 MINUTE
STARTS (NOW() + INTERVAL 1 MINUTE)
DO
DELETE FROM ta_table WHERE ti_time > NOW() - INTERVAL 2 HOUR;
SELECT COUNT(1) FROM ta_table;
SELECT SLEEP(62);
SELECT COUNT(1) FROM ta_table;
This worked for me when ta_table was MyISAM. It just kept running using InnoDB. This may be the sticking point.
There is a manual page on Time and Date functions.
The best way to compare date and time and get minutes back is probably to use one of the following:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)
Note that while TIMEDIFF
works with times, TIMESTAMPDIFF
requires a date component - and the values are switched around for TIMESTAMPDIFF
.
You can return hours with either of these:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)
Note that when using DATEDIFF
, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).
To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:
SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)
This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.
Best Answer
Like @a_horse_with_no_name told on his comment, the differences are documented in here, but here is some information:
Size:
datetime
- uses 8 bytes for each fieldtimestamp
- uses 4 bytes for each field (half of the size)Range:
datetime
-1000-01-01 00:00:00
to9999-12-31 23:59:59
timestamp
-1970-01-01 00:00:01
UTC to2038-01-19 03:14:07
UTCTimezone:
as @ypercube mention,
timestamp
converts your data to utc and store it, and when you retrieve it, it converts from utc to your timezone connection.Concept:
datetime
- Is a calendar date(same point in time can be different depends on timezone).timestamp
- Is a point in time, does not matter the timezone your are.Suggestion:
The 2 main differences are
range
andsize
, then think, do you really need dates bigger then2038-01-19 03:14:07
at the moment(at the moment, no in the future!)? If no, go withtimestamp
for now, when you reach a point where you need a date range outsidetimestamp
range, convert it todatetime
.