This question already has an answer here:
I have fairly simple table, in which the field lastloginon is timestamp and the date stored in it looks like this: 2013-03-17 17:12:58
How can i select all records that are isnerted in last 20 minutes?
I am trying with the following code:
SELECT * FROM who_is_online WHERE lastloginon > (now() - interval 20 minute)
which produce wrong set of results that is event the qyery is executed at 15:30 it display 4 records which are inserted at:
2013-03-18 13:13:36
2013-03-18 13:35:12
2013-03-18 14:43:42
2013-03-18 14:55:34
Anyone know what i am doing wrong?
Here is the table export, just in hope that someone can spot what i am doing wrong:
CREATE TABLE IF NOT EXISTS `who_is_online` (
`recid` int(11) NOT NULL auto_increment,
`username` varchar(255) default NULL,
`lastloginon` timestamp NOT NULL default CURRENT_TIMESTAMP,
`memberip` varchar(255) default NULL,
`sessionid` varchar(255) NOT NULL,
`active` tinyint(4) default NULL,
PRIMARY KEY (`recid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;
--
-- Dumping data for table `who_is_online`
--
INSERT INTO `who_is_online` (`recid`, `username`, `lastloginon`, `memberip`, `sessionid`, `active`) VALUES
(7, ' admin', '2013-03-17 14:43:43', '87.202.163.222', '7kve2idmdbd47aksab4j9hqg74', 1),
(8, ' mmitechnic', '2013-03-17 15:16:39', '5.54.84.144', 'mofgpldp2lu30enhitvak9m4t3', 1),
(9, ' theodor71', '2013-03-17 17:12:58', '85.75.243.246', '4enpk49oi7cg0blumgsd0lu0m7', 1),
(10, ' xElfiex', '2013-03-18 12:38:05', '5.55.27.203', 'tr003d6qbd71v2i5grnuako362', 1),
(11, ' admin', '2013-03-18 12:56:06', '85.74.166.110', '7kve2idmdbd47aksab4j9hqg74', 1),
(12, ' xElfiex', '2013-03-18 13:13:36', '5.55.27.203', 'tr003d6qbd71v2i5grnuako362', 1),
(13, ' admin', '2013-03-18 13:35:12', '85.74.166.110', '7kve2idmdbd47aksab4j9hqg74', 1),
(14, ' admin', '2013-03-18 14:43:42', '85.74.166.110', '7kve2idmdbd47aksab4j9hqg74', 1),
(15, ' admin', '2013-03-18 14:55:34', '85.74.166.110', '7kve2idmdbd47aksab4j9hqg74', 1),
(16, ' admin', '2013-03-18 15:32:27', '85.74.166.110', '7kve2idmdbd47aksab4j9hqg74', 1);
Regards,Zoran
</div>
try date_sub(now(), interval 20 minute) instead of (now() - interval 20 minute)
It looks to me like you may have a timezone issue. It's hard to tell without knowing more about your server and client configuration.
But you might try this WHERE clause ...
lastloginon >= UNIX_TIMESTAMP() - (20 * 60)
This will compare your column numerically to the unix timestamp of twenty minutes ago.
The time on the mysql server is different than what you probably expect it to be. In order to find the time on the server try using this query:
select now();
Now, your query is correct, but your insert to the table is incorrect. Instead of using
$lastlogin = date('Y-m-d H:i:s', strtotime("$lastlogin + 2 hours"));
on the site side, leave this field empty and use your "default CURRENT_TIMESTAMP".
on a different note, pay attention to the fact that username has an unexplained leading space.