Often in PostgreSQL you may want to get the last day of the previous month.
You can use the following basic syntax to do so:
SELECT (date_trunc('month', now()) - interval '1 day')::date AS last_day;
This particular example will return the last day of the previous month.
Here is how this syntax works:
- First, we use the now() function to return the current date and time.
- We then use the date_trunc() function to only return the month, formatted as the first day of the month.
- Lastly, we subtract 1 day to get the last day of the previous month.
Note: To get the last day of the previous month for each date in a table, simply replace now() with the column name that contains the dates.
The following examples show how to use this syntax in practice.
Example 1: Get Last Day of the Previous Month in PostgreSQL
This article is currently being written on 8/29/2024.
We can use the following syntax to get the last day of the previous month:
SELECT (date_trunc('month', now()) - interval '1 day')::date AS last_day;
last_day
------------
2024-07-31
Notice that this returns 7/31/2024, which indeed represents the last day of the previous month.
Example 2: Get Last Day of the Previous Month for Each Date in Table in PostgreSQL
Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:
-- create table named 'emp'
CREATE TABLE emp (
empID INT PRIMARY KEY,
start_date DATE,
sales INT
);
-- add rows to the table
INSERT INTO emp VALUES (0001, '2021-04-01', 134);
INSERT INTO emp VALUES (0002, '2022-07-01', 201);
INSERT INTO emp VALUES (0003, '2022-08-15', 155);
INSERT INTO emp VALUES (0004, '2022-09-01', 98);
INSERT INTO emp VALUES (0005, '2023-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-02-20', 52);
INSERT INTO emp VALUES (0007, '2024-05-15', 50);
-- view all rows in table
SELECT * FROM emp;
empid | start_date | sales
-------+------------+-------
1 | 2021-04-01 | 134
2 | 2022-07-01 | 201
3 | 2022-08-15 | 155
4 | 2022-09-01 | 98
5 | 2023-11-23 | 65
6 | 2024-02-20 | 52
7 | 2024-05-15 | 50
Suppose that we would like to get the last day of the previous month for each date in the start_date column.
We can use the following syntax to do so:
SELECT empID,
start_date,
(date_trunc('month', start_date) - interval '1 day')::date
AS last_day
FROM emp;
empid | start_date | last_day
-------+------------+------------
1 | 2021-04-01 | 2021-03-31
2 | 2022-07-01 | 2022-06-30
3 | 2022-08-15 | 2022-07-31
4 | 2022-09-01 | 2022-08-31
5 | 2023-11-23 | 2023-10-31
6 | 2024-02-20 | 2024-01-31
7 | 2024-05-15 | 2024-04-30
The new column named last_day contains the last day of the previous month for each corresponding date in the start_date column.
You can use similar syntax to get the last day of the previous month for a date column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Month from Date
PostgreSQL: How to Extract Year and Month from Date
PostgreSQL: How to Calculate Difference Between Dates in Months
