TapeGun007 Posted October 20, 2015 Share Posted October 20, 2015 I seem to frequently beat my head against a wall when trying to compare anything to do with dates or times. This has me stumped. In my database I have a TimeStamp. I simply want to check if 20 minutes has passed. I have tried at least 20 different examples of code, none of which work. $Current = date("Y-m-d h:i:s"); $TimeStamp = $row['TimeStamp']; I presume I want to keep the date and time, but there is obviously something in the whole date and time functions that I'm not grasping. I know it has to be something very simple. If you have any links that explain comparisons like this, I would be more than happy to read. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 20, 2015 Share Posted October 20, 2015 (edited) SELECT * FROM your_table WHERE timestampdiff(minute, timestamp_column, Now()) > 20 Edited October 20, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
hansford Posted October 21, 2015 Share Posted October 21, 2015 (edited) $now = time(); $past = strtotime($row['TimeStamp']); $elapsed_minutes = floor(($now - $past) / 60); if ($elapsed_minutes >= 20) { // do something } Edited October 21, 2015 by hansford Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 21, 2015 Share Posted October 21, 2015 is your database holding a UNIX Timestamp or a MYSQL Timestamp, they are not the same things (though a MYSQL Timestamp is internally stored as a unix timestamp and has the same range restrictions.) a UNIX Timestamp is an integer that represents the number of seconds since '1970-01-01 00:00:00' UTC. a MYSQL Timestamp is formatted/treated as either a 'YYYY-MM-DD HH:MM:SS' string or a YYYYMMDDHHMMSS number. any mysql or php functions that operate on your data would need to be specific to what your data type actually is. are you using a UNIX Timestamp/integer or you are using a MYSQL Timestamp data type? 1 Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted October 21, 2015 Author Share Posted October 21, 2015 @benanamen, Yes, I'm aware of that method. But I need the data for both the over 20 minutes and under 20 minutes, so that will not work. @hansford - doesn't work. It thinks the time has passed every time. The output for echo "$now - $past - $elapsed_minutes"; 1445456993 - 1445412904 - 734 @mac_gyver - I made a change today as I realized I don't need a timestamp field. I don't want the field to auto update. So I set it to datetime instead. This is in the mySQL db, but now you got me curious... how do you tell if it's UNIX or mySQL? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted October 21, 2015 Share Posted October 21, 2015 a unix timestamp would be stored as an integer data type. the functions used to produce the integer value would be those that produce a unix timestamp, such as php's time()/mktime()/strtotime() functions or mysql's UNIX_TIMESTAMP() function. a mysql timetamp would be defined as a mysql timestamp data type, with the values being supplied to it as either a 'YYYY-MM-DD HH:MM:SS' string or a YYYYMMDDHHMMSS number. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 21, 2015 Share Posted October 21, 2015 (edited) @benanamen, Yes, I'm aware of that method. But I need the data for both the over 20 minutes and under 20 minutes, so that will not work. You left out that detail my brutha. SELECT *, IF(timestampdiff(minute, timestamp_column, Now()) > 20, 'More than 20', 'Less Than 20') AS status FROM your_table Edited October 21, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted October 21, 2015 Author Share Posted October 21, 2015 a unix timestamp would be stored as an integer data type. the functions used to produce the integer value would be those that produce a unix timestamp, such as php's time()/mktime()/strtotime() functions or mysql's UNIX_TIMESTAMP() function. a mysql timetamp would be defined as a mysql timestamp data type, with the values being supplied to it as either a 'YYYY-MM-DD HH:MM:SS' string or a YYYYMMDDHHMMSS number. Ok, so then yes, it was a timestamp... of course, I now have made it a datetime field instead. Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted October 21, 2015 Author Share Posted October 21, 2015 @benanamen, oh very nice. I'm not so good at SQL actually. Here was my original SQL: $sql = "SELECT * FROM LoginSecurity WHERE TempCode ='$temppassword' AND SalesID='$ID'"; I'm not sure how to implement your code with mine but I'll take a stab at it. I'm guessing: $sql = "SELECT * IF(timestampdiff(minute, TimeStamp, Now()) > 20, 'More than 20', 'Less Than 20') AS status FROM LoginSecurity WHERE TempCode ='$temppassword' AND SalesID='$ID'"; Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 21, 2015 Share Posted October 21, 2015 (edited) But, but, you said... need the data for both the over 20 minutes and under 20 minutes Your WHERE condition is only going to get you a record(s) matching the SalesID and TempCode Did you really name your timestamp column timestamp? You know thats a MySQL Reserved Word dont you? I now have made it a datetime field instead Why did you do that? You were OK with a timestamp column for what you are doing. Its the same exact format as datetime. The query I gave you may not work on datetime, it uses the timestampdiff function. Edited October 21, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 21, 2015 Share Posted October 21, 2015 (edited) FYI: DATETIME:The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. TIMESTAMP: data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Edited October 21, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted October 21, 2015 Author Share Posted October 21, 2015 Benanamen, Yeah, my bad. That's because I have two different pages I'm working on at the same time. One will show a list of all (admin) and one will show just a single entry (user). For the users page I really need something like this (but it is incorrect): "SELECT * FROM LoginSecurity WHERE timestampdiff(minute, TimeRecord, Now()) < 20 AND SalesID = '$ID' AND TempCode ='$temppassword' "; Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 21, 2015 Share Posted October 21, 2015 I can help you more exactly if you can provide an sql dump of your DB so I can run it locally. Quote Link to comment Share on other sites More sharing options...
Solution TapeGun007 Posted October 22, 2015 Author Solution Share Posted October 22, 2015 benanamen, It's quite simple (supposedly): I have a table called "Security" Security ---------- ID SalesID SecurityAnswer TimeRecord TempCode Attempts LoginSecurityID All I want to do now (regardless of what I posted previously) is check if the TimeRecord is less than 20 minutes. I need to also ensure that the TempCode matches, and the ID is correct. "SELECT * FROM LoginSecurity WHERE timestampdiff(minute, TimeRecord, CURRENT_TIMESTAMP()) < 20 AND SalesID = '$ID' AND TempCode ='$temppassword' I've tried CURRENT_TIMESTAMP, and I tried using Now()....it just doesn't seem to work and so I know it's a syntax thing. To test I'm running the following after the query: $rs=$con->query($sql); $rs->data_seek(0); $row = $rs->fetch_assoc(); if($row > 0){ echo "Record found"; }else{ echo "Record not found"; } Quote Link to comment Share on other sites More sharing options...
TapeGun007 Posted October 22, 2015 Author Share Posted October 22, 2015 I literally just put that code in there and typed it up here without testing. The above code actually does what I wanted it to do. LMAO. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.