Hi Chaps,
I have a MySQL table with a timestamp and an interval column:
CREATE TABLE IF NOT EXISTS `tbl_checks` (
`check_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`check_title` varchar(50) NOT NULL,
`check_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`check_file` varchar(50) DEFAULT NULL,
`check_interval` varchar(3) DEFAULT NULL,
PRIMARY KEY (`check_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
--
-- Dumping data for table `tbl_checks`
--
INSERT INTO `tbl_checks` (`check_id`, `check_title`, `check_time`, `check_file`, `check_interval`) VALUES
(1, 'check_1', '2011-04-20 00:00:07', 'check_1.php', '1'),
(2, 'check_2', '2011-03-28 00:03:33', 'check_2.php', '28'),
(3, 'check_3', '2011-04-20 00:00:03', 'check_3.php', '1'),
(4, 'check_4', '2011-04-16 00:00:08', 'check_4.php', '5');
I need a select query that will give me the ‘checks’ that are overdue. Something like: select if NOW is after timestamp+interval?
?
Interval is measured in…?
Apologies, Interval in DAYS
Should be something along the lines of…
SELECT * FROM table WHERE NOW() < DATE_ADD(check_time, INTERVAL check_interval DAYS)
Note: This function (NOW()) is time-specific. So it will only return results that occured before the current time on their expected due date.
r937
April 22, 2011, 2:13am
5
you were close, you just had the wrong inequality
for overdue, i think you want WHERE NOW() > check_time + INTERVAL check_interval DAYS
Hi Guys,
I’ve tried both variations, but I’m getting a syntax
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DAYS’ at line 1
Any ideas?
Er… might need to be DAY instead of DAYS. is not quite awake yet
Good stuff, thanks a lot guys,
SELECT * FROM tbl_checks WHERE NOW() > check_time + INTERVAL check_interval DAY;
Sorted
r937
April 22, 2011, 5:52pm
10
i hate it when i propagate an error
some lousy data integrity filter i am, eh