blocks|key|290897|text|这两个解决方案都包括上个月的最后一天，也包括“今天”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|290898|对于date列：|offset|length|style|CODE|290899|SELECT+*
FROM+++tbl
WHERE++my_date+BETWEEN+date_trunc('month',+now())::date+-+1
+++++++++++++++AND+++++now()::date|code-block|syntax|javascript|290900|您可以从date+(但不是从timestamp)中减去普通整数值来减去days。这是最简单、最快的方法。|290901|对于timestamp列：|290902|SELECT+*
FROM+++tbl
WHERE++my_timestamp+>=+date_trunc('month',+now())+-+interval+'1+day'
AND++++my_timestamp+<++date_trunc('day'++,+now())+%2B+interval+'1+day'|290903|请注意，我对第二个条件使用了<运算符，以获得精确的结果(~“明天之前”)。|290904|在第二个查询中，我没有强制转换为date。相反，我添加了一个interval+'1+day'，以避免来回转换。|290905|请看一下手册中的date+/+time+types和functions。|290906|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/interactive/datatype-datetime.html|1|http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TABLE^0|0|2|4|0|0|4|4|E|9|0|2|9|0|0|E|1|0|G|4|U|G|0|8|H|0|Q|9|1|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@$D|1A|E|1B|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|1C|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1D|8|@$D|1E|E|1F|F|G]|$D|1G|E|1H|F|G]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1I|8|@$D|1J|E|1K|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|J|7|1L|8|@]|9|@]|A|$K|L]]|$1|S|3|T|5|6|7|1M|8|@$D|1N|E|1O|F|G]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1P|8|@$D|1Q|E|1R|F|G]|$D|1S|E|1T|F|G]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1U|8|@]|9|@$D|1V|E|1W|1|1X]|$D|1Y|E|1Z|1|20]]|A|$]]|$1|Y|3|-4|5|6|7|21|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]|16|$5|11|12|13|A|$14|17]]]]

Both solutions include the last day of the previous month and also include all of &quot;today&quot;.
<h3>For a <code>date</code> column:</h3>
<pre><code>SELECT *
FROM tbl
WHERE my_date BETWEEN date_trunc('month', now())::date - 1
 AND now()::date
</code></pre>
You can subtract plain integer values from a <code>date</code> (but not from a <code>timestamp</code>) to subtract days. This is the simplest and fastest way.
<h3>For a <code>timestamp</code> column:</h3>
<pre><code>SELECT *
FROM tbl
WHERE my_timestamp &gt;= date_trunc('month', now()) - interval '1 day'
AND my_timestamp &lt; date_trunc('day' , now()) + interval '1 day'
</code></pre>
Note that I use the <code>&lt;</code> operator for the second condition to get precise results (~ &quot;before tomorrow&quot;).
I do not cast to <code>date</code> in the second query. Instead I add an <code>interval '1 day'</code>, to avoid casting back and forth.
Have a look at <a href="http://www.postgresql.org/docs/current/interactive/datatype-datetime.html" rel="noreferrer">date / time types</a> and <a href="http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TABLE" rel="noreferrer">functions</a> in the manual.

blocks|key|1398196|text|获取上个月/上个月的日期：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1398197|SELECT+(date_trunc('month',+now())::date+-+1)+as+last_month_date|code-block|syntax|javascript|1398198|结果:+2012-11-30|1398199|获取上个月/上个月的number+of+days：|1398200|SELECT+DATE_PART('days',+date_trunc('month',+now())::date+-+1)+last_month_days|1398201|结果:+30|1398202|entityMap^0|A|2|0|0|0|A|E|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|O|3|P|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|13|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|T|$]]

For getting date of previous/last month:

<pre><code>SELECT (date_trunc('month', now())::date - 1) as last_month_date
</code></pre>

Result: 2012-11-30

For getting number of days of previous/last month:

<pre><code>SELECT DATE_PART('days', date_trunc('month', now())::date - 1) last_month_days
</code></pre>

Result: 30

blocks|key|1394775|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1394776|SELECT+...
WHERE++date_field+between+(date_trunc('MONTH',+now())+-+INTERVAL+'1+day')::date+
++++++++++++++++++++++and+now()::date+
+++++++...|code-block|syntax|javascript|1394777|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|$]]

Try this:

<pre><code>SELECT ...
WHERE date_field between (date_trunc('MONTH', now()) - INTERVAL '1 day')::date 
 and now()::date 
 ...
</code></pre>

blocks|key|4330964|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4330965|select+current_date+-+cast((date_part('day',+current_date)+%2B+1)+as+int)|code-block|syntax|javascript|4330966|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|$]]

Try

<pre><code>select current_date - cast((date_part('day', current_date) + 1) as int)
</code></pre>

blocks|key|377048|text|从http://wiki.postgresql.org/wiki/Date_LastDay获取，并修改为仅返回一个月中的天数|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|377049|++++CREATE+OR+REPLACE+FUNCTION+calc_days_in_month(date)
++++RETURNS+double+precision+AS
++++$$
++++++SELECT+EXTRACT(DAY+FROM+(date_trunc('MONTH',+$1)+%2B+INTERVAL+'1+MONTH+-+1+++++++++day')::date);
++++$$+LANGUAGE+'sql'+IMMUTABLE+STRICT;


++++select+calc_days_in_month('1999-05-01')|code-block|syntax|javascript|377050|返回31|377051|entityMap|0|LINK|mutability|MUTABLE|url|http://wiki.postgresql.org/wiki/Date_LastDay^0|1|18|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

take from <a href="http://wiki.postgresql.org/wiki/Date_LastDay" rel="nofollow">http://wiki.postgresql.org/wiki/Date_LastDay</a>, and modified to return just the days in a month

<pre><code> CREATE OR REPLACE FUNCTION calc_days_in_month(date)
 RETURNS double precision AS
 $$
 SELECT EXTRACT(DAY FROM (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date);
 $$ LANGUAGE 'sql' IMMUTABLE STRICT;


 select calc_days_in_month('1999-05-01')
</code></pre>

returns 31

blocks|key|1394864|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1394865|你可以使用下面的函数：|offset|length|style|BOLD|1394866|CREATE+OR+REPLACE+FUNCTION+fn_GetLastDayOfMonth(DATE)
RETURNS+DATE+AS
$$
++++SELECT+(date_trunc('MONTH',+$1)+%2B+INTERVAL+'1+MONTH+-+1+day')::DATE;
$$+LANGUAGE+'sql'+
IMMUTABLE+STRICT;|code-block|syntax|javascript|1394867|示例执行：|1394868|SELECT+*FROM+fn_GetLastDayOfMonth(NOW()::DATE);|1394869|entityMap^0|0|8|3|0|0|0|5|0|0^^$0|@$1|2|3|-4|4|5|6|R|7|@]|8|@]|9|$]]|$1|A|3|B|4|5|6|S|7|@$C|T|D|U|E|F]]|8|@]|9|$]]|$1|G|3|H|4|I|6|V|7|@]|8|@]|9|$J|K]]|$1|L|3|M|4|5|6|W|7|@$C|X|D|Y|E|F]]|8|@]|9|$]]|$1|N|3|O|4|I|6|Z|7|@]|8|@]|9|$J|K]]|$1|P|3|-4|4|5|6|10|7|@]|8|@]|9|$]]]|Q|$]]

<a href="http://www.dbrnd.com/2017/01/postgresql-how-to-find-last-day-of-the-month/" rel="nofollow noreferrer">Reference is taken from this blog:</a>

You can use below function:

<pre><code>CREATE OR REPLACE FUNCTION fn_GetLastDayOfMonth(DATE)
RETURNS DATE AS
$$
 SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::DATE;
$$ LANGUAGE 'sql' 
IMMUTABLE STRICT;
</code></pre>

Sample executions:

<pre><code>SELECT *FROM fn_GetLastDayOfMonth(NOW()::DATE);
</code></pre>

I need to query a PostgreSQL database to determine records that fall within today's date and the last day of the previous month. In other words, I'd like to retrieve everything that falls between December 31, 2011 and today. This query will be re-used each month, so next month, the query will be based upon the current date and January 31, 2012.

I've seen <a href="http://wiki.postgresql.org/wiki/Date_LastDay" rel="noreferrer">this option</a>, but I'd prefer to avoid using a function (if possible).

How do I determine the last day of the previous month using PostgreSQL?

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我需要查询一个PostgreSQL数据库，以确定今天的日期和上个月的最后一天之间的记录。换句话说，我想检索从2011年12月31日到今天的所有数据。此查询将在每个月重复使用，因此下个月将基于当前日期和2012年1月31日进行查询。我见过，但我更希望避免使用函数(如果可能的话)。

问如何使用PostgreSQL确定上个月的最后一天？
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用PostgreSQL确定上个月的最后一天？EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用PostgreSQL确定上个月的最后一天？
EN