Comparing time to a timestamp

[TapeGun007]

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.

[benanamen]

SELECT * 

FROM   your_table 

WHERE  timestampdiff(minute, timestamp_column, Now()) > 20 

 

Edited October 20, 2015 by benanamen

[hansford]

$now = time();

$past = strtotime($row['TimeStamp']);


$elapsed_minutes = floor(($now - $past) / 60);


if ($elapsed_minutes >= 20)

{

    // do something

}

 

Edited October 21, 2015 by hansford

[mac_gyver]

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?

[TapeGun007]

@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?

[mac_gyver]

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.

[benanamen]

@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

[TapeGun007]

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.

[TapeGun007]

@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'";

[benanamen]

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

[benanamen]

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

[TapeGun007]

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'
            ";

[benanamen]

I can help you more exactly if you can provide an sql dump of your DB so I can run it locally.

[TapeGun007]

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";
}

[TapeGun007]

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.