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

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

提问by Huuuze

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.

我需要查询 PostgreSQL 数据库以确定今天日期和上个月最后一天的记录。换句话说，我想检索 2011 年 12 月 31 日到今天之间的所有内容。此查询将每月重复使用，因此下个月，查询将基于当前日期和 2012 年 1 月 31 日。

I've seen this option, but I'd prefer to avoid using a function (if possible).

我见过这个选项，但我更愿意避免使用函数（如果可能的话）。

回答by Erwin Brandstetter

Both solutions includethe last day of the previous month and also includeall of "today".

两种解决方案都包括上个月的最后一天，还包括所有“今天”。

For a datecolumn:

对于date列：

SELECT *
FROM   tbl
WHERE  my_date BETWEEN date_trunc('month', now())::date - 1
               AND     now()::date

You can subtract plain integer values from a date(but not from a timestamp) to subtract days. This is the simplest and fastest way.

您可以从 a date（但不能从 a timestamp）中减去普通整数值以减去天数。这是最简单、最快的方法。

For a timestampcolumn:

对于timestamp列：

SELECT *
FROM   tbl
WHERE  my_timestamp >= date_trunc('month', now()) - interval '1 day'
AND    my_timestamp <  date_trunc('day'  , now()) + interval '1 day'

Note that I use the <operator for the second condition to get precise results (~ "before tomorrow").

请注意，我将<运算符用于第二个条件以获得精确的结果（~“明天之前”）。

I do not cast to datein the second query. Instead I add an interval '1 day', to avoid casting back and forth.

我不会date在第二个查询中强制转换为。相反，我添加了一个interval '1 day', 以避免来回转换。

Have a look at date / time typesand functionsin the manual.

查看手册中的日期/时间类型和功能。

回答by Usersbs

For getting dateof previous/last month:

获取上个月/上个月的日期：

SELECT (date_trunc('month', now())::date - 1) as last_month_date

Result: 2012-11-30

结果：2012-11-30

For getting number of daysof previous/last month:

获取上个月/上个月的天数：

SELECT DATE_PART('days', date_trunc('month', now())::date - 1) last_month_days

Result: 30

结果：30

回答by Usersbs

Try this:

试试这个：

SELECT ...
WHERE  date_field between (date_trunc('MONTH', now()) - INTERVAL '1 day')::date 
                      and now()::date 
       ...

回答by theglauber

Try

尝试

select current_date - cast((date_part('day', current_date) + 1) as int)

回答by user2601381

take from http://wiki.postgresql.org/wiki/Date_LastDay, and modified to return just the days in a month

取自http://wiki.postgresql.org/wiki/Date_LastDay，并修改为仅返回一个月中的天数

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


    select calc_days_in_month('1999-05-01')

returns 31

返回 31

回答by Anvesh

Reference is taken from this blog:

参考来自此博客：

You can use below function:

您可以使用以下功能：

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

Sample executions:

示例执行：

SELECT *FROM fn_GetLastDayOfMonth(NOW()::DATE);