entityMap|blocks|key|c403c|text|也许你可以使用这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5du9k|SELECT+*+
++FROM+table+
++WHERE+
++++EXTRACT(week+FROM+datecolumn)+=+EXTRACT(week+FROM+NOW())
++++AND
++++EXTRACT(isoyear+FROM+datecolumn)+=+EXTRACT(isoyear+FROM+NOW())|code-block|syntax|javascript|ao74e|week是ISO-8601周数。根据定义，ISO周从星期一开始，一年的第一周包含该年的1月4日。换句话说，一年的第一个星期四在那一年的第一周。|offset|length|style|CODE|2upv1|在ISO周编号系统中，1月初的日期可以是前一年的52周或53周的一部分，而12月下旬的日期可以是下一年的第一周的一部分。|317lj|例如，2005-01-01是2004年53周的一部分，2006-01-01是2005年52周的一部分，而2012-12-31是2013年第一周的一部分。|8q7jv|为了获得一致的结果，建议将isoyear字段与week字段一起使用。|bejma|如果您需要自定义(非ISO)周编号-您将不得不手工计算。|6rhu6^0|0|0|0|4|0|0|0|D|7|N|4|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|W|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|X|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|Y|9|@$J|Z|K|10|L|M]]|A|@]|B|$]]|$2|N|4|O|6|7|8|11|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|12|9|@]|A|@]|B|$]]|$2|R|4|S|6|7|8|13|9|@$J|14|K|15|L|M]|$J|16|K|17|L|M]]|A|@]|B|$]]|$2|T|4|U|6|7|8|18|9|@]|A|@]|B|$]]|$2|V|4|-4|6|7|8|19|9|@]|A|@]|B|$]]]]

Perhaps you can use something like this:

<pre><code>SELECT * 
 FROM table 
 WHERE 
 EXTRACT(week FROM datecolumn) = EXTRACT(week FROM NOW())
 AND
 EXTRACT(isoyear FROM datecolumn) = EXTRACT(isoyear FROM NOW())
</code></pre>

The <code>week</code> is ISO-8601 week number. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year. 

In the ISO week-numbering system, it is possible for early-January dates to be part of the 52nd or 53rd week of the previous year, and for late-December dates to be part of the first week of the next year. 

For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005, while 2012-12-31 is part of the first week of 2013. 

It's recommended to use the <code>isoyear</code> field together with <code>week</code> to get consistent results.

If you need custom (non-ISO) week numbering - you will have to craft your own calculation.

entityMap|blocks|key|dmggp|text|我会推荐以下两个条件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|c1idm|where+
++++date_column+>=+current_date+-+extract(dow+from+current_date)+*+interval+'1+day'
++++and+date_column+<+current_date+-+(extract(dow+from+current_date)+-+8)+*+interval+'1+day'|code-block|syntax|javascript|40t5a|Postgres的date_trunc(week,+...)在周一开始，所以我们需要一些更复杂的东西，使用extract(dow+from+...)，它在周日返回0。|offset|length|style|CODE|2tmeh|这种方法的优点是它是SARGeable的，因为没有函数应用于被过滤的列。这意味着这将很好地利用date列上的索引。|h18u^0|0|0|9|L|1H|L|29|1|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|Q|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|R|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|S|9|@$J|T|K|U|L|M]|$J|V|K|W|L|M]|$J|X|K|Y|L|M]]|A|@]|B|$]]|$2|N|4|O|6|7|8|Z|9|@]|A|@]|B|$]]|$2|P|4|-4|6|7|8|10|9|@]|A|@]|B|$]]]]

I would recommend the following pair of conditions:

<pre><code>where 
 date_column &gt;= current_date - extract(dow from current_date) * interval '1 day'
 and date_column &lt; current_date - (extract(dow from current_date) - 8) * interval '1 day'
</code></pre>

Postgres' <code>date_trunc(week, ...)</code> starts weeks on Monday, so we need something a little more complicated, using <code>extract(dow from ...)</code>, which returns <code>0</code> on Sundays.

The advantage of this approach is that it is SARGeable, since no function is applied to the column being filtered. This means that this would happily take advantage of an index on the date column.

entityMap|blocks|key|c308o|text|我会使用date_trunc()，但就像这样。对于当前一周：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|82rk|where+datecolumn+>=+date_trunc('week',+now())+and
++++++datecolumn+<+date_trunc('week',+now())+%2B+interval+'1+week'|code-block|syntax|javascript|2vutv|对于一年中的第n周，这就更棘手了。我认为这就是你想要的：|ffuu7|where+datecolumn+>=+(date_trunc('week',+now())+-
+++++++++++++++++++++(extract(week+from+now())+-+1)+*+interval+'1+week'+%2B
+++++++++++++++++++++<n>+*+interval+'1+week'
++++++++++++++++++++)+and
++++++datecolumn+<+(date_trunc('week',+now())+-
+++++++++++++++++++++(extract(week+from+now())+-+1)+*+interval+'1+week'+%2B
+++++++++++++++++++++(<n>+%2B+1)+*+interval+'1+week'
++++++++++++++++++++)|cv8eg|这两种方法都是结构化的，因此计算不在列上，因此它们与使用索引兼容。|22bqe^0|4|C|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|S|9|@$A|T|B|U|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|V|9|@]|E|@]|F|$J|K]]|$2|L|4|M|6|7|8|W|9|@]|E|@]|F|$]]|$2|N|4|O|6|I|8|X|9|@]|E|@]|F|$J|K]]|$2|P|4|Q|6|7|8|Y|9|@]|E|@]|F|$]]|$2|R|4|-4|6|7|8|Z|9|@]|E|@]|F|$]]]]

I would use <code>date_trunc()</code>, but like this. For the current week:

<pre><code>where datecolumn &gt;= date_trunc('week', now()) and
 datecolumn &lt; date_trunc('week', now()) + interval '1 week'
</code></pre>

For the nth week of the year, this is trickier. I think this does what you want:

<pre><code>where datecolumn &gt;= (date_trunc('week', now()) -
 (extract(week from now()) - 1) * interval '1 week' +
 &lt;n&gt; * interval '1 week'
 ) and
 datecolumn &lt; (date_trunc('week', now()) -
 (extract(week from now()) - 1) * interval '1 week' +
 (&lt;n&gt; + 1) * interval '1 week'
 )
</code></pre>

Both of these are structured so the computations are NOT on the columns, so they are compatible with using indexes.

How to query (SELECT) in Postgresql, so that the results of a column with different dates, are between Sunday and Saturday of the current week.

Query fake example: 

<pre><code>SELECT * FROM table WHERE datecolumn BETWEEN CURRENT WEEK
</code></pre>

In another query, I have the number of the week in the year. How to make a SELECT for these dates, applying in the WHERE clause the specific week number in the specific year.

Query fake example: 

<pre><code>SELECT * FROM table WHERE datecolumn BETWEEN WEEK15 FROM year 2020
</code></pre>

Query filtering by week

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

 如何在Postgresql中查询(SELECT)，使日期不同的列的结果在当前周的周日到周六之间。 查询伪例： SELECT * FROM table WHERE datecolumn BETWEEN CURRENT WEEK 在另一个查询中，我得到了一年中的第几周。如何选择这些日期，并在WHERE子句中应用特定年份中...

问按周过滤查询
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问按周过滤查询EN