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
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.
You might want to use Max(id) and "sent" instead Max('id') and "'sent'"
Edit: ^--- This is partially wrong it is valid to use `id` and `sent`, but 'id' and 'sent' is not valid
Like this:
SELECT Max(id) FROM `mytable` WHERE TIMESTAMPDIFF(MINUTE, sent, NOW()) > 5
EDIT: The problem is that you have stored "sent" as an integer, in order to use it with TIMESTAMPDIFF you'll have to convert it into the DATE_TIME format. Here is how you can do that:
TIMESTAMPDIFF(MINUTE,FROM_UNIXTIME(sent),NOW())
And the end result:
SELECT Max(id) FROM `mytable` WHERE TIMESTAMPDIFF(MINUTE,FROM_UNIXTIME(sent),NOW()) > 5Some extra reading: Convert timestamp to date in MySQL query
</div>