Time/Date Query

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.

you were close, you just had the wrong inequality :wink:

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

Yeah, it’s day.

Good stuff, thanks a lot guys,

SELECT * FROM tbl_checks WHERE NOW() > check_time + INTERVAL check_interval DAY;

Sorted

i hate it when i propagate an error

some lousy data integrity filter i am, eh

:blush: