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

Determining Last Day of Previous Month in PostgreSQL

To retrieve records within the current date and the last day of the previous month, you can use PostgreSQL's powerful date and time functions. Here are both date and timestamp-based solutions without the use of a function:

For Date Columns:

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

Subtracting integers from a date (not a timestamp) conveniently deducts days.

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'

Note that the '<' operator is used in the second condition to achieve "before tomorrow" precision. Casting to a date is avoided in the second query by adding an interval '1 day.'

Consult the PostgreSQL manual for further insights into date / time types and functions.

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