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
– tadman, Commented Jun 16, 2014 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
– wuno, Commented Jun 16, 2014 at 20:36

Community · Accepted Answer · 2023-11-17 20:42:18Z

3

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.

edited Nov 17, 2023 at 20:42

CommunityBot

11 silver badge

answered Jun 16, 2014 at 20:38

Mark Miller

7,4472 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wuno Over a year ago

Hey thanks for that, would you mind explaining what if (time() - $data->created_time < 86400) { is exactly?

wuno Over a year ago

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.

Collectives™ on Stack Overflow

Comparing Current Time To Timestamp In Mysql DB With PHP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related