mysql_query日期型_在mysqli_query中，是否可以比較日期(“Y-m-d H:i:s”)和時間()?

I would like to select from database 10 most popular posts posted in last 24 hours based on likes. In database for each post I stored time when the post was posted using date("Y-m-d H:i:s") in column post_date.

我想從數據庫中選擇最受歡迎的10個帖子。在數據庫中，當post在post_date中使用日期(“y -m- dh: I:s”)時，我存儲的每個帖子的時間。

I need something like this:

我需要這樣的東西:

$most_popular = mysqli_query($mysqli_connect, "SELECT * FROM posts WHERE (time() - post_date) < 86400 ORDER BY likes DESC LIMIT 10");

4 个解决方案

#1

2

you can do following:

你可以做以下:

SELECT *

FROM posts

WHERE post_date > DATE_SUB(NOW(), INTERVAL 1 DAY)

ORDER BY likes DESC

LIMIT 10

#2

2

I would just compare the post_date with 24 hours before:

我只是比較一下post_date和24小時之前:

$last_24_hours = date("Y-m-d H:i:s", strtotime("-24 Hours"));

$query = sprintf("SELECT *

FROM posts

WHERE post_date > '%s'

ORDER BY likes DESC

LIMIT 10", $last_24_hours);

$most_popular = mysqli_query($mysqli_connect, $query);

#3

0

Your question is: "Is it possible to compare date (“Y-m-d H:i:s”) with time() inside mysqli_query?"

您的問題是:“是否有可能在mysqli_query中比較日期(“Y-m-d H:i:s”)和時間()?”

The answer is yes:

答案是肯定的:

SELECT TIME('2003-12-31 01:02:03'); // returns '01:02:03'

NOTE: This CAN'T handle many dates, if that's your question.

注意:如果這是你的問題的話，這不能解決很多約會。

#4

0

SELECT *

FROM posts

WHERE post_date >= (now() - INTERVAL 1 DAY)

ORDER BY ...

etc...