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

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).

我见过这个选项,但我宁愿避免使用函数(如果可能的话)。

6 个解决方案

#1

Both solutions include the last day of the previous month and also include all of "today".

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

For a date column:

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.

您可以从日期(但不是从时间戳)中减去纯整数值来减去天数。这是最简单,最快捷的方式。

For a timestamp column:

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 date in the second query. Instead I add an interval '1 day', to avoid casting back and forth.

我没有在第二个查询中转换为日期。相反,我添加一个间隔“1天”,以避免来回转换。

Have a look at date / time types and functions in the manual.

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

#2

For getting date of previous/last month:

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

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

Result: 2012-11-30

For getting number of days of previous/last month:

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

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

Result: 30

#3

Try this:

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

#4

Try

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

#5

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', $1) + INTERVAL '1 MONTH - 1         day')::date);
    $$ LANGUAGE 'sql' IMMUTABLE STRICT;


    select calc_days_in_month('1999-05-01')

returns 31

#6

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', $1) + INTERVAL '1 MONTH - 1 day')::DATE;
$$ LANGUAGE 'sql' 
IMMUTABLE STRICT;

Sample executions:

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