如何选择Max（id）与Mysql中两个时间戳之间的差异优于五分钟？

Question

I am trying to select the Max('id') with the difference of 5 minutes between two timestamp in mysql.

My timestamp field is sent

I did

SELECT Max('id') FROM `mytable` WHERE TIMESTAMPDIFF(MINUTE, `sent`, NOW()) > 5

But this did not work. It sent me and empty result

This is my table structure

CREATE TABLE IF NOT EXISTS `mytable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `from_id` int(10) unsigned NOT NULL,
  `to_id` int(10) unsigned NOT NULL,
  `message` text NOT NULL,
  `sent` int(10) unsigned NOT NULL DEFAULT '0',
  `read_statu` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `to` (`to_id`),
  KEY `from` (`from_id`),
  KEY `direction` (`is_deleted`),
  KEY `read` (`read_statu`),
  KEY `sent` (`sent`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=201 ;

INSERT INTO `mytable` (`id`, `from_id`, `to_id`, `message`, `sent`, `read_statu`, `is_deleted`) VALUES
(1, 1, 2, 'Juste un test pour moi meme', 1425729597, 1, 0),
(2, 2, 1, 'Hello', 1425729612, 1, 0),
(3, 2, 1, ' <3  (L)  :prayer:  :tkh:  :heart:  :-$ ', 1425729922, 1, 0),
(4, 2, 1, ' <3  (L)  :prayer:  :tkh:  :heart:  :-$ ', 1425729927, 1, 0),
(5, 1, 2, 'Ok', 1426010868, 0, 0);

Please How to select Max(id) with the difference between two timestamp superior to five minute in Mysql ?

Thanks

Answer 1

id should be escaped with backticks not single quotes

SELECT Max(`id`) FROM `mytable` ...

By using single quotes there, MySQL was using the literal string 'id' instead of the column id.

Also, you need to understand that TIMESTAMPDIFF does not accept UNIX timestamps as the arguments, but rather valid MySQL datetime expressions. This function is used to return a timestamp-like interval value given two datetime expression values.

IMO, you would help yourself greatly by storing sent as a datetime field rather than Unix timestamp. Unix timestamps are generally a poor design decision in MySQL tables if you ever need to do any calculations/filtering, etc. on the field.

Assuming you change sent to a datetime field, your query could look like this:

SELECT MAX(`id`)
FROM `myTable`
WHERE `sent` > DATESUB(NOW(), INTERVAL 5 MINUTE)

Assuming you want to keep your Unix timestamp field format the query might look like this:

SELECT MAX(`id`)
FROM `myTable`
WHERE `sent` > UNIXTIMESTAMP(DATESUB(NOW(), INTERVAL 5 MINUTE))

You see you just have an extra conversion to make to work with UNIX timestamp.

Note also the format of the WHERE clause here. I did not use the field being filtered against as part of the calculation as you did in your query. Your approach would disallow the use of an index on sent for the query. By keeping all the time calculations on one side of the compare, we are, in essence, able to make the datetime calculation once for the entire query and then compare each row against that value using index on sent.

To see this difference in query speed, run that second query given above (with your current use of timestamps for sent) and compare against this query below, which would be analogous to you current approach:

SELECT MAX(`id`)
FROM `myTable`
WHERE TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(`sent`), NOW()) > 5

You should see a significant difference at large table sizes.