How do I determine the last day of the previous month using PostgreSQL?

by

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'

I use the < operator for the second condition to get precise results (read: “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.

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

More Related Contents:

  1. PostgreSQL: running count of rows for a query ‘by minute’
  2. Time zone storage in data type “timestamp with time zone”
  3. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  4. How do you do date math that ignores the year?
  5. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  6. Subtract hours from the now() function
  7. Truncate timestamp to arbitrary intervals
  8. Sort by column ASC, but NULL values first?
  9. Using an Alias column in the where clause in Postgresql
  10. How can I select the first day of a month in SQL?
  11. How to return result of a SELECT inside a function in PostgreSQL?
  12. Possible to perform cross-database queries with PostgreSQL?
  13. Reset auto increment counter in postgres
  14. GROUP BY and aggregate sequential numeric values
  15. Safely rename tables using serial primary key columns
  16. get JOIN table as array of results with PostgreSQL/NodeJS
  17. How Can I use “Date” Datatype in sql server?
  18. PL/pgSQL checking if a row exists
  19. Get the distinct sum of a joined table column
  20. How much disk-space is needed to store a NULL value using postgresql DB?
  21. PostgreSQL convert columns to rows? Transpose?
  22. PostgreSQL delete with inner join
  23. Regular expression in PostgreSQL LIKE clause
  24. Postgres Error: More than one row returned by a subquery used as an expression
  25. Update a column of a table with a column of another table in PostgreSQL
  26. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  27. What is the internal representation of datetime in sql server?
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment