PostgreSQL: How to Get the Number of Days in a Month

Often in PostgreSQL you may want to get the number of days in a given month.

You can use the following syntax to do so:

SELECT *,
       DATE_PART('days', DATE_TRUNC('month', start_date) 
                         + '1 MONTH'::INTERVAL
                         - '1 DAY'::INTERVAL) AS num_days
FROM emp;

This particular example gets the number of days in a month for each date in the start_date column of the emp table and saves the results to a new column name num_days.

Here is how this syntax works:

• First, we use DATE_TRUNC() to truncate the date to the first of the month.
• Then, we add 1 month to get the first day of the next month.
• Then, we subtract 1 day to get the last day of the current month.
• Lastly, we use DATE_PART() with the ‘days’ parameter to return only the day portion of the last day of the month.

The following example shows how to use this syntax in practice.

Example: How to Get Number of Days in a Month 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, '2024-02-01', 134);
INSERT INTO emp VALUES (0002, '2024-03-01', 201);
INSERT INTO emp VALUES (0003, '2024-04-22', 155);
INSERT INTO emp VALUES (0004, '2024-05-26', 98);
INSERT INTO emp VALUES (0005, '2024-06-28', 65);
INSERT INTO emp VALUES (0006, '2024-07-20', 52);
INSERT INTO emp VALUES (0007, '2024-08-22', 50);

-- view all rows in table
SELECT * FROM emp;

 empid | start_date | sales 
-------+------------+-------
     1 | 2024-02-01 |   134
     2 | 2024-03-01 |   201
     3 | 2024-04-22 |   155
     4 | 2024-05-26 |    98
     5 | 2024-06-28 |    65
     6 | 2024-07-20 |    52
     7 | 2024-08-22 |    50

Suppose that we would like to find the number of days in each month for each date in the start_date column.

We can use the following syntax to do so:

SELECT *,
       DATE_PART('days', DATE_TRUNC('month', start_date) 
                         + '1 MONTH'::INTERVAL
                         - '1 DAY'::INTERVAL) AS num_days
FROM emp;

 empid | start_date | sales | num_days 
-------+------------+-------+----------
     1 | 2024-02-01 |   134 |       29
     2 | 2024-03-01 |   201 |       31
     3 | 2024-04-22 |   155 |       30
     4 | 2024-05-26 |    98 |       31
     5 | 2024-06-28 |    65 |       30
     6 | 2024-07-20 |    52 |       31
     7 | 2024-08-22 |    50 |       31

The new column named num_days displays the number of days in the month for the corresponding date in the start_date column.

Feel free to use similar syntax to find the number of days in a month in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Delete Empty Rows in Table
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: Select Rows Where Date is Less Than