CODEWITHSUNDEEP
phpmysql
wuno
wuno

Reputation: 9865

Comparing Current Time To Timestamp In Mysql DB With PHP

I am inserting values from the FB API into my DB. One of those values is a timestamp for each one of the events creation time.

The way it is set up right now its grabbing all of the users tagged_places. I am trying to compare the time the tagged_place was created to the Current Time and if its not within the past 24 hours I want it to be ignored.

I know I need to convert the timestamp in the DB for a way for php to read it. I find this

Click Here

But I am not understanding how to implement it.

Here is my insert query to the DB,

$stmt = $con->prepare('
    INSERT INTO taggedPlaces
    (id, created_time, place_id, latitude, longitude, name)
    VALUES
    (?, ?, ?, ?, ?, ?)
');

foreach($graphObject['tagged_places']->data as $data) {
   $stmt->execute(array(
       $data->id,
       $data->created_time,
       $data->place->id,
       $data->place->location->latitude,
       $data->place->location->longitude,
       $data->place->name
   ));
}

How can I change this to only insert the values in data that have been created in the past 24 hours?

Upvotes: 1

Views: 1778

Answers (1)

Mark Miller
Mark Miller

Reputation: 7447

Assuming $data->created_time is a datetime or similar format:

foreach($graphObject['tagged_places']->data as $data) {
   
   if (time() - strtotime($data->create_time) < 86400) { ...

       $stmt->execute(array(
           $data->id,
           $data->created_time,
           $data->place->id,
           $data->place->location->latitude,
           $data->place->location->longitude,
           $data->place->name
       ));
   }
}

If $data->created_time is already a timestamp, then strtotime() is not necessary:

if (time() - $data->created_time < 86400) { ...

Some explanation:

  • A Unix Timestamp is the number of seconds since Jan 1, 1970.

  • time() returns the current unix timestamp.

  • strtotime() converts a string to a unix timestamp.

  • time() - strtotime($data->created_time) is the difference in seconds between the current time and the time stored.

  • 86400 is the number of seconds in 24 hours.

So the statement

if (time() - strtotime($data->created_time) < 86400) { ...

says:

If the difference, in seconds, between the current date/time and the date/time of the tagged place is less than 24 hours (86400 seconds), proceed with insert.

Upvotes: 3

Related Questions