-1

I have a database table with an auto-update column which is required to be in the TIMESTAMP format, this saves dates in the form YYYY-MM-DD HH:mm:ss for each time a row is updated.

On reading statements that date comparisons are (possibly very) processor heavy, the preferred method seems to be to use MySQL BETWEEN statement to check and return updates that have occurred in the last 24 hours.

A reference: https://stackoverflow.com/a/14104364/3536236

My SQL

I have removed some details that take up space that are outside the scope of this question, such as some columns

-- Generation Time: Oct 14, 2015 at 04:54 PM
-- Server version: 5.5.45-cll
-- PHP Version: 5.4.31

--
-- Table structure for table `check_log`
--

CREATE TABLE IF NOT EXISTS `check_log` (
  `table_id` int(8) NOT NULL AUTO_INCREMENT,
  `last_action` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip_addr` varchar(60) NOT NULL,
  `submit_fail` varchar(1) NOT NULL,
  PRIMARY KEY (`fail_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=14 ;

--
-- Dumping data for table `check_log`
--
INSERT INTO `check_log` (`table_id`, `last_action`, `ip_addr`, `submit_fail`) VALUES
(2, '2015-10-14 14:08:30', '92.99.252.185', 'N'),
(3, '2015-10-14 14:09:23', '92.99.252.185', 'N'),
(4, '2015-10-14 14:09:25', '92.99.252.185', 'N'),
(5, '2015-10-14 14:09:38', '92.99.252.185', 'N'),
(6, '2015-10-14 14:14:22', '92.99.252.185', 'N'),
(7, '2015-10-14 14:17:13', '92.99.252.185', 'N'),
(8, '2015-10-14 14:20:51', '92.99.252.185', 'N'),
(9, '2015-10-14 14:20:52', '92.99.252.185', 'N'),
(10, '2015-10-14 14:50:34', '92.99.252.185', 'N'),
(11, '2015-10-14 15:29:07', '92.99.252.185', 'N'),
(12, '2015-10-14 15:31:04', '92.99.252.185', 'N'),
(13, '2015-10-14 15:32:00', '92.99.252.185', 'N');

My Query
Now, my query wants to return all the rows that fit the criteria that have been updated in the last 24hours. So:

SELECT * FROM `check_log` WHERE `ip_addr` = '92.99.252.185' AND 
(`last_action` BETWEEN date_sub(CURDATE() , INTERVAL -1 DAY ) AND CURDATE()) 
AND `submit_fail` = 'N'

I wrote the query in this shape because I wanted to explore how BETWEEN ... AND ... handled other ANDS in the same query, and hence for my own clarity I encased the BETWEEN statement in brackets ().

I have tried a range of minorly different syntaxes for this query including:

SELECT * FROM `check_login` WHERE `ip_addr` = '92.99.252.185' AND 
(DATE_FORMAT(`last_action`, '%Y-%m-%d') BETWEEN date_sub(CURDATE() , INTERVAL -1 DAY ) AND CURDATE())

and pure date check:

SELECT * FROM `check_login` WHERE 
`last_action` BETWEEN date_sub(CURDATE() , INTERVAL -1 DAY ) AND CURDATE()

Each time the MySQL returns Zero Rows (not an error) but zero rows found. I have viewed and compared at least a dozen similar answers on SO about the comparison of dates and am at a bit of a loss how I'm not getting the rows returned that I'm expecting with my query.

(I am ideally wanting to use the BETWEEN form as this table will, when in use be reaching several thousands of rows. )

  • What can I do to make the comparison work?
  • How does the BETWEEN clause handle other ANDs, is it suitable to encase in brackets (for clarity)
  • Is there a more efficient / suitable method to compare timestamp column dates?
Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132

1 Answers1

0

It appears that DATE_SUB() is subtraction so I did not need to do -1 on the INTERVAL <value> DAY section of the SQL, however, the INTERVAL does accept negative values but that would overall be a subtraction of a negative and so a +1 Day interval.

I had originally thought for some reason DATE_SUB had stood for substitution as the allowance of negative values in the value part - to me - meant that there was no need for a data addition function as well.

I wasted half a day reading up and trying to work out how this logic worked.

Martin
  • 22,212
  • 11
  • 70
  • 132