php check against a mysql timestamp

Question

I have the following function but have a problem in that my timestamp field in the database is a mysql timestamp type which is in format '2012-08-30 11:31:41' and the strtotime('-12 hours'); is giving a unix timestamp.

whats the solution?

public function checkIfItemVisited($user_id, $item_id) {

    $timeago = strtotime('-12 hours');
    $params = array(
        ':user_id' => $user_id,
        ':item_id' => $item_id,
        ':timestamp' => $timeago
    );
    $sql = "SELECT `visit_id` FROM `item_visits` WHERE `user_id` = :user_id AND `item_id` = :item_id AND `timestamp` > :timestamp";
    $stmt = $this->query($sql, $params);

    if($stmt->rowCount() > 0) :
        return false;
    else :
        $this->countVisit($user_id, $item_id);
    endif;
}

I don't understand what you're trying to do here, nor what your problem is. — Madara's Ghost
– Madara's Ghost, Commented Sep 1, 2012 at 7:00

Rajat Singhal · Accepted Answer · 2012-09-01 07:00:44Z

2

To solve your timestamp comparison problem, you can use UNIX_TIMESTAMP() in mysql

$sql = "SELECT `visit_id`
        FROM `item_visits`
        WHERE `user_id` = :user_id
               AND `item_id` =  :item_id
               AND UNIX_TIMESTAMP(timestamp) > :timestamp";

answered Sep 1, 2012 at 7:00

Rajat Singhal

11.3k5 gold badges40 silver badges57 bronze badges

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

1 Comment

user557846 Over a year ago

@VinceLowe I cant imagine it is very efficient converting the timestamp every time rather than using the built in functions -well it's your call.

Spudley · Accepted Answer · 2012-09-01 11:01:33Z

2

 ...   AND `timestamp` > NOW() - INTERVAL 12 HOUR ...

edited Sep 1, 2012 at 11:01

Spudley

169k39 gold badges240 silver badges309 bronze badges

answered Sep 1, 2012 at 7:01

user557846

Comments

seeker · Accepted Answer · 2012-09-01 07:16:42Z

0

Try this:

SELECT FROM_UNIXTIME(1196440219);

If you want vise-wersa conversion, use this:

SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');

Source: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

edited Sep 1, 2012 at 7:16

answered Sep 1, 2012 at 7:00

seeker

3,3637 gold badges42 silver badges72 bronze badges

Collectives™ on Stack Overflow

php check against a mysql timestamp

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related