How to Get the Last Day of the Previous Month in PostgreSQL?

Determining the Last Day of the Previous Month in PostgreSQL

When querying a database to retrieve records within a specific date range, it's often necessary to include the last day of the previous month. This range can vary each month, depending on the current date.

Without a Function

To determine the last day of the previous month without using a function, you can use the following query for a date column:

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

The date_trunc('month', now()) function returns the first day of the current month. By subtracting one day from this result using the subtraction operator, we get the last day of the previous month.

For a timestamp column, the query is slightly different:

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

The date_trunc('month', now()) function returns the start of the current month as a timestamp. To get the last day of the previous month, we subtract one day using the - interval '1 day' expression.

For the second condition, we add one day to the date_trunc('day' , now()) result using interval '1 day' to ensure that the range includes all of "today."

With a Function

If you prefer using a function, you can utilize the LAST_DAY() function to achieve the same result:

SELECT *
FROM tbl
WHERE my_date BETWEEN LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND NOW()

This function explicitly calculates the last day of the previous month based on the current date.

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