douge3492
2012-09-01 06:57
浏览 65
已采纳

php检查mysql时间戳

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;
}
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongshanya2008 2012-09-01 07:00
    已采纳

    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";
    
    已采纳该答案
    打赏 评论
  • dqknycyt92288 2012-09-01 07:00

    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

    打赏 评论
  • doucong4535 2012-09-01 07:01
     ...   AND `timestamp` > NOW() - INTERVAL 12 HOUR ...
    
    打赏 评论

相关推荐 更多相似问题