How to Query PostgreSQL Data from the End of Last Month to Today?

How to Retrieve Records Spanning from the End of Last Month to Today in PostgreSQL

Seeking a solution to determine the last day of the previous month and use it in a query to filter records between that date and today in a PostgreSQL database?

To cater to this requirement, here are two options that avoid using functions:

For Date Columns:

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

Subtracting an integer from a date subtracts days, providing a quick and efficient method.

For Timestamp Columns:

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

Utilizing the < operator for the second condition ensures precise results, while adding an interval instead of casting to a date streamlines the process.

Both solutions include the last day of the previous month and all of today. By incorporating these queries in your code, you can extract the desired data dynamically based on the current date.

The above is the detailed content of How to Query PostgreSQL Data from the End of Last Month to Today?. For more information, please follow other related articles on the PHP Chinese website!