CODEWITHSUNDEEP
mysql
user1592380
user1592380

Reputation: 36247

Find mysql records from last 3 days

I want to get the records for the last 3 days. I have the following mysql table:

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`emails` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`phones` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`history` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`insert_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=259 ;

INSERT INTO `mytable` (`id`, `emails`, `phones`, `history`, `insert_date`)
VALUES
(118, 'PLEASE SET',  '[email protected]', 'None','2015-01-13'),
(237, 'PLEASE SET', '[email protected]', 'gomez', '2015-01-11');

I tried :

SELECT * FROM `mytable` WHERE DATE_SUB(CURDATE(), `insert_date`) > 3

This gives:

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 '`insert_date`

How can I get this working?

Upvotes: 0

Views: 1820

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

DATE_SUB expects the second argument to be an INTERVAL expression, and will return a date/datetime result, not an integer.

You can try this:

SELECT *
FROM `mytable`
WHERE `insert_date` >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)

Please note that it is important to compare unmodified insert_date value against the calculated date you are comparing against, as this would allow for use of an index on insert_date. You should add this index on your table to optimize the query.

Upvotes: 5

Related Questions