blocks|key|969243|text|假设您的date列是一个实际的MySQL+date列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|969244|SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(NOW(),+INTERVAL+1+DAY)+ORDER+BY+score+DESC;++++++++
SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(NOW(),+INTERVAL+1+WEEK)+ORDER+BY+score+DESC;
SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(NOW(),+INTERVAL+1+MONTH)+ORDER+BY+score+DESC;|code-block|syntax|javascript|969245|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Assuming your date column is an actual MySQL date column:

<pre><code>SELECT * FROM jokes WHERE date &gt; DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC; 
SELECT * FROM jokes WHERE date &gt; DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date &gt; DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;
</code></pre>

blocks|key|753694|text|Nathan的回答将提供过去24小时、168小时和744小时的笑话，而不是今天、本周和本月的笑话。如果这是你想要的，很好，但我认为你可能在寻找一些不同的东西。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|753695|使用他的代码，在中午，你会得到昨天中午开始的笑话，今天中午结束的笑话。如果你真的想听今天的笑话，试试下面的：|753696|SELECT+*+FROM+jokes+WHERE+date+>=+CURRENT_DATE()+ORDER+BY+score+DESC;++|code-block|syntax|javascript|753697|你将不得不做一些与当前周、月等稍有不同的事情，但你得到了这个想法。|753698|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Nathan's answer will give you jokes from last 24, 168, and 744 hours, NOT the jokes from today, this week, this month. If that's what you want, great, but I think you might be looking for something different.

Using his code, at noon, you will get the jokes beginning yesterday at noon, and ending today at noon. If you really want today's jokes, try the following:

<pre><code>SELECT * FROM jokes WHERE date &gt;= CURRENT_DATE() ORDER BY score DESC; 
</code></pre>

You would have to do something a little different from current week, month, etc., but you get the idea.

blocks|key|753730|text|尝试使用日期和时间函数(MONTH()，YEAR()，DAY()，MySQL+Manual)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|753731|本周：|753732|SELECT+*+FROM+jokes+WHERE+WEEKOFYEAR(date)=WEEKOFYEAR(NOW());|code-block|syntax|javascript|753733|上周：|753734|SELECT+*+FROM+jokes+WHERE+WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;|753735|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html^0|X|C|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|13|8|@]|9|@]|C|$I|J]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Try using date and time functions (MONTH(), YEAR(), DAY(), <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html" rel="noreferrer">MySQL Manual</a>)

This week:

<pre><code>SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW());
</code></pre>

Last week:

<pre><code>SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;
</code></pre>

blocks|key|753755|text|似乎每个人都将日期称为表中的一列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|753756|我认为这不是一个好的做法。单词date可能只是某些编码语言(可能是Oracle)中的关键字，所以请将列名称date改为可能的JDate。|753757|因此，下面的方法会更好地工作：|753758|SELECT+*+FROM+jokes+WHERE+JDate+>=+CURRENT_DATE()+ORDER+BY+JScore+DESC;|code-block|syntax|javascript|753759|因此，我们有一个名为Jokes的表，其中包含列JScore和JDate。|753760|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Everybody seems to refer to date being a column in the table. 
I dont think this is good practice. The word date might just be a keyword in some coding language (maybe Oracle) so please change the columnname date to maybe JDate. 
So will the following work better:

<pre><code>SELECT * FROM jokes WHERE JDate &gt;= CURRENT_DATE() ORDER BY JScore DESC;
</code></pre>

So we have a table called Jokes with columns JScore and JDate.

blocks|key|753782|text|Nathan的答案非常接近，但是它将返回一个浮点结果集。随着时间的推移，记录将从结果集浮动到结果集上。在NOW()上使用DATE()函数将从创建静态结果集的日期中剥离时间元素。由于date()函数应用于now()而不是实际的date列，因此性能应该更高，因为对日期列应用诸如date()之类的函数会禁止MySql使用索引。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|753783|要使结果集保持静态，请使用：|753784|SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(DATE(NOW()),+INTERVAL+1+DAY)+
ORDER+BY+score+DESC;

SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(DATE(NOW()),+INTERVAL+1+WEEK)+
ORDER+BY+score+DESC;

SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(DATE(NOW()),+INTERVAL+1+MONTH)+
ORDER+BY+score+DESC;|code-block|syntax|javascript|753785|entityMap^0|1G|5|1O|6|2I|6|2T|5|34|4|3T|6|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|N|$]]

Nathan's answer is very close however it will return a floating result set. As the time shifts, records will float off of and onto the result set. Using the <code>DATE()</code> function on <code>NOW()</code> will strip the time element from the date creating a static result set. Since the <code>date()</code> function is applied to <code>now()</code> instead of the actual <code>date</code> column performance should be higher since applying a function such as <code>date()</code> to a date column inhibits MySql's ability to use an index.

To keep the result set static use:

<pre><code>SELECT * FROM jokes WHERE date &gt; DATE_SUB(DATE(NOW()), INTERVAL 1 DAY) 
ORDER BY score DESC;

SELECT * FROM jokes WHERE date &gt; DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK) 
ORDER BY score DESC;

SELECT * FROM jokes WHERE date &gt; DATE_SUB(DATE(NOW()), INTERVAL 1 MONTH) 
ORDER BY score DESC;
</code></pre>

blocks|key|753829|text|当月：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|753830|SELECT+*+FROM+jokes+WHERE+YEAR(date)+=+YEAR(NOW())+AND+MONTH(date)=MONTH(NOW());|code-block|syntax|javascript|753831|当前周：|753832|SELECT+*+FROM+jokes+WHERE+WEEKOFYEAR(date)+=+WEEKOFYEAR(NOW());|753833|当天：|753834|SELECT+*+FROM+jokes+WHERE+YEAR(date)+=+YEAR(NOW())+AND+MONTH(date)+=+MONTH(NOW())+AND+DAY(date)+=+DAY(NOW());|753835|这将仅选择当前月份、真正的星期和真正的今天:-)|753836|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Current month:

<pre><code>SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date)=MONTH(NOW());
</code></pre>

Current week:

<pre><code>SELECT * FROM jokes WHERE WEEKOFYEAR(date) = WEEKOFYEAR(NOW());
</code></pre>

Current day:

<pre><code>SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND DAY(date) = DAY(NOW());
</code></pre>

This will select only current month, really week and really only today :-)

blocks|key|753872|text|那么，这个解决方案将帮助您选择仅当月、当前周和仅今天|type|unstyled|depth|inlineStyleRanges|entityRanges|data|753873|SELECT+*+FROM+games+WHERE+games.published_gm+=+1+AND+YEAR(addedon_gm)+=+YEAR(NOW())+AND+MONTH(addedon_gm)+=+MONTH(NOW())+AND+DAY(addedon_gm)+=+DAY(NOW())+ORDER+BY+addedon_gm+DESC;|code-block|syntax|javascript|753874|对于每周添加的帖子：|753875|WEEKOFYEAR(addedon_gm)+=+WEEKOFYEAR(NOW())|753876|对于每月添加的帖子：|753877|MONTH(addedon_gm)+=+MONTH(NOW())|753878|对于每年增加的帖子：|753879|YEAR(addedon_gm)+=+YEAR(NOW())|753880|您将获得准确的结果，其中只显示今天添加的游戏，否则您可能会显示：“没有找到今天的新游戏”。使用ShowIF记录集是空事务。|753881|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Well, this solution will help you select only current month, current week and only today

<pre><code>SELECT * FROM games WHERE games.published_gm = 1 AND YEAR(addedon_gm) = YEAR(NOW()) AND MONTH(addedon_gm) = MONTH(NOW()) AND DAY(addedon_gm) = DAY(NOW()) ORDER BY addedon_gm DESC;
</code></pre>

For Weekly added posts:

<pre><code>WEEKOFYEAR(addedon_gm) = WEEKOFYEAR(NOW())
</code></pre>

For Monthly added posts:

<pre><code>MONTH(addedon_gm) = MONTH(NOW())
</code></pre>

For Yearly added posts:

<pre><code>YEAR(addedon_gm) = YEAR(NOW())
</code></pre>

you'll get the accurate results where show only the games added today, otherwise you may display: "No New Games Found For Today". Using ShowIF recordset is empty transaction.

blocks|key|753908|text|“今天”的一个更好的解决方案是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|753909|SELECT+*+FROM+jokes+WHERE+DATE(date)+=+DATE(NOW())|code-block|syntax|javascript|753910|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

A better solution for "today" is:

<pre><code>SELECT * FROM jokes WHERE DATE(date) = DATE(NOW())
</code></pre>

blocks|key|969531|text|我认为使用NOW()函数获取时间差是不正确的。因为通过NOW()函数，你每次计算过去的24小时。您必须改用CURDATE()。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|969532|++++function+your_function($time,+$your_date)+{
++++if+($time+==+'today')+{
++++++++$timeSQL+=+'+Date($your_date)=+CURDATE()';
++++}
++++if+($time+==+'week')+{
++++++++$timeSQL+=+'+YEARWEEK($your_date)=+YEARWEEK(CURDATE())';
++++}
++++if+($time+==+'month')+{
++++++++$timeSQL+=+'+Year($your_date)=Year(CURDATE())+AND+Month(`your_date`)=+Month(CURDATE())';
++++}

++++$Sql+=+"SELECT+*+FROM+jokes+WHERE+".$timeSQL
++++return+$Result+=+$this->db->query($Sql)->result_array();
}|code-block|syntax|javascript|969533|entityMap^0|5|5|R|5|1H|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

I think using <code>NOW()</code> function is incorrect for getting time difference. Because by <code>NOW()</code> function every time your are calculating the past 24 hours.
You must use <code>CURDATE()</code> instead. 

<pre><code> function your_function($time, $your_date) {
 if ($time == 'today') {
 $timeSQL = ' Date($your_date)= CURDATE()';
 }
 if ($time == 'week') {
 $timeSQL = ' YEARWEEK($your_date)= YEARWEEK(CURDATE())';
 }
 if ($time == 'month') {
 $timeSQL = ' Year($your_date)=Year(CURDATE()) AND Month(`your_date`)= Month(CURDATE())';
 }

 $Sql = "SELECT * FROM jokes WHERE ".$timeSQL
 return $Result = $this-&gt;db-&gt;query($Sql)-&gt;result_array();
}
</code></pre>

blocks|key|969588|text|以MySQL格式获取上周的数据。这对我来说是有效的，即使在一年中也是如此。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|969589|select+*+from+orders_order+where+YEARWEEK(my_date_field)=+YEARWEEK(DATE_SUB(CURRENT_DATE(),+INTERVAL+1+WEEK));|code-block|syntax|javascript|969590|获取当前周的数据|969591|select+*+from+orders_order+where+YEARWEEK(date_sold)=+YEARWEEK(CURRENT_DATE());|969592|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

To get last week's data in MySQL. This works for me even across the year boundries. 

<pre class="lang-sql prettyprint-override"><code>select * from orders_order where YEARWEEK(my_date_field)= YEARWEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK));
</code></pre>

To get current week's data 

<pre class="lang-sql prettyprint-override"><code>select * from orders_order where YEARWEEK(date_sold)= YEARWEEK(CURRENT_DATE());
</code></pre>

blocks|key|754013|text|您可以使用单个查询来做同样的事情|type|unstyled|depth|inlineStyleRanges|entityRanges|data|754014|SELECT+sum(if(DATE(dDate)=DATE(CURRENT_TIMESTAMP),earning,null))+astodays,
+++++++sum(if(YEARWEEK(dDate)=YEARWEEK(CURRENT_DATE),earning,null))+as+weeks,+
+++++++IF((MONTH(dDate)+=+MONTH(CURRENT_TIMESTAMP())+AND+YEAR(dDate)+=+YEAR(CURRENT_TIMESTAMP())),sum(earning),0)+AS+months,
+++++++IF(YEAR(dDate)+=+YEAR(CURRENT_TIMESTAMP()),sum(earning),0)+AS+years,+
+++++++sum(fAdminFinalEarning)+as+total_earning+FROM+`earning`|code-block|syntax|javascript|754015|希望这能起作用。|754016|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can do same thing using single query

<pre><code>SELECT sum(if(DATE(dDate)=DATE(CURRENT_TIMESTAMP),earning,null)) astodays,
 sum(if(YEARWEEK(dDate)=YEARWEEK(CURRENT_DATE),earning,null)) as weeks, 
 IF((MONTH(dDate) = MONTH(CURRENT_TIMESTAMP()) AND YEAR(dDate) = YEAR(CURRENT_TIMESTAMP())),sum(earning),0) AS months,
 IF(YEAR(dDate) = YEAR(CURRENT_TIMESTAMP()),sum(earning),0) AS years, 
 sum(fAdminFinalEarning) as total_earning FROM `earning`
</code></pre>

Hope this works.

blocks|key|969664|text|我建议你这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|969665|SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(NOW(),+INTERVAL+1+DAY)+ORDER+BY+score+DESC;++++++++
SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(NOW(),+INTERVAL+1+WEEK)+ORDER+BY+score+DESC;
SELECT+*+FROM+jokes+WHERE+date+>+DATE_SUB(NOW(),+INTERVAL+1+MONTH)+ORDER+BY+score+DESC;|code-block|syntax|javascript|969666|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I suggest you to do like that:
<pre class="lang-sql prettyprint-override"><code>SELECT * FROM jokes WHERE date &gt; DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC; 
SELECT * FROM jokes WHERE date &gt; DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date &gt; DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;
</code></pre>

I imagine this is pretty simple, but can't figure it out. I'm trying to make a few pages - one which will contain results selected from my mysql db's table for today, this week, and this month. The dates are entered when the record is created with <code>date('Y-m-d H:i:s');</code>. Here's what I have so far:

day where date>(date-(60*60*24))

<pre><code> "SELECT * FROM jokes WHERE date&gt;(date-(60*60*24)) ORDER BY score DESC"
</code></pre>

week where date>(date-(60*60*24*7))

<pre><code> "SELECT * FROM jokes WHERE date&gt;(date-(60*60*24*7)) ORDER BY score DESC"
</code></pre>

month (30 days) where date>(date-(60*60*24*30))

<pre><code> "SELECT * FROM jokes WHERE date&gt;(date-(60*60*24*30)) ORDER BY score DESC"
</code></pre>

Any ideas would be much appreciated. Thanks!

Select records from today, this week, this month php mysql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想这很简单，但我想不出来。我正在尝试制作几个页面-其中一个页面将包含从我的mysql数据库的表中选择的今天、本周和本月的结果。日期是在使用date('Y-m-d H:i:s');创建记录时输入的。这是我到目前为止所知道的：日期where (date>-(60*60*24)) "SELECT * FROM jokes ...

问从今天、本周、本月选择记录php mysql
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从今天、本周、本月选择记录php mysqlEN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从今天、本周、本月选择记录php mysql
EN