How to Efficiently Find the Last Day of the Previous Month in PostgreSQL?

Resolving the Last Day of the Previous Month in PostgreSQL

Determining the last day of the previous month is a common requirement in database querying. In PostgreSQL, there are efficient methods to retrieve records within a specified date range.

One straightforward approach is to use the date_trunc() function. For a date column, use:

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

By subtracting 1 from the date_trunc('month', now()) expression and casting it to a date, you effectively get the first day of the previous month (e.g., December 1, 2011). Adding 1 to now()::date includes all of today's date in the range.

For a timestamp column, a slightly different approach is needed:

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

In this case, interval '1 day' is used to ensure that the previous month's last day (e.g., December 31, 2011) is fully included in the range. The interval '1 day' expression on the upper bound ensures that "today" is also included.

Using these methods, you can accurately determine the last day of the previous month andretrieve records that fall within the desired date range in PostgreSQL.

The above is the detailed content of How to Efficiently Find the Last Day of the Previous Month in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!