我有以下函数,但有一个问题,我在数据库中的时间戳字段是mysql时间戳类型,格式为2012-08-30 11:31:41',strtotime('-12 hours');正在提供unix时间戳。
解决方案是什么?
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;
}
要解决时间戳比较问题,可以在mysql 中使用UNIX_TIMESTAMP()
$sql = "SELECT `visit_id`
FROM `item_visits`
WHERE `user_id` = :user_id
AND `item_id` = :item_id
AND UNIX_TIMESTAMP(timestamp) > :timestamp";
... AND `timestamp` > NOW() - INTERVAL 12 HOUR ...
试试这个:
SELECT FROM_UNIXTIME(1196440219);
如果你想转换虎钳,请使用这个:
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
来源:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-单次