Comparing Current Time To Timestamp In Mysql DB With PHP

Question

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?

What is your schema? At least provide a representative sample of `created_time`? — tadman, Jun 16 '14 at 20:34
Im sorry if im noy beung clear. U havw never deal with this before. I said timestamp in my question what else would you need to know? Thank you for your help. — wuno, Jun 16 '14 at 20:36

Mark Miller · Accepted Answer · 2014-06-16T21:05:35.110

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.

Hey thanks for that, would you mind explaining what `if (time() - $data->created_time < 86400) {` is exactly? — wuno, Jun 16 '14 at 20:44
Thanks so much that worked perfectly. Just for someone else that comes across this I did have to convert it. Thank you for adding that in your answer. If you don't mind I would appreciate you explaining the 86400 but thanks again. — wuno, Jun 16 '14 at 20:52

Comparing Current Time To Timestamp In Mysql DB With PHP

1 Answers1