Jump to content

Comparing time to a timestamp


TapeGun007
Go to solution Solved by TapeGun007,

Recommended Posts

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.
Link to comment
Share on other sites

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?

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

@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 by benanamen
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

@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'";
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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'
            ";
Link to comment
Share on other sites

  • Solution

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";
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.