We can use the following method in PostgreSQL to return the first day of a given month.
This could be the first day of the current month, or the first day of the month based on a date that we specify.
Getting the first day of the month allows us to perform further calculations on the resulting date, like adding a certain number of days to the start of the month, etc.
Start of the Current Month
Here’s an example that returns the first day of the current month:
SELECT date_trunc('month', now());Result:
2022-04-01 00:00:00+10
This uses PostgreSQL’s date_trunc() function to return the results we want. This function truncates a date/time value to a specified precision.
In this case I use the now() function to return the current date, and the 'month' argument modifies that date to the beginning of the month.
We can cast it to a date value if required:
SELECT date_trunc('month', now())::date;Result:
2022-04-01
Here it is again, along with the actual date that I ran the example:
SELECT
now()::date AS "Current Date",
date_trunc('month', now())::date AS "Start of Month";Result:
+--------------+----------------+ | Current Date | Start of Month | +--------------+----------------+ | 2022-04-09 | 2022-04-01 | +--------------+----------------+
Start of a Specified Month
It doesn’t have to be the start of the current month. We can specify any date, and it will return the start of the month, based on that date.
Example:
SELECT date_trunc('month', date '2030-07-14')::date;Result:
2030-07-01
We can then use the result to perform other actions on it. For example, we can add a given number of days to the result like this:
SELECT date_trunc('month', date '2030-07-14')::date + 20;Result:
2030-07-21
Database Example
Here’s an example that uses dates from a database:
SELECT
rental_date,
date_trunc('month', rental_date)::date AS "The 1st of the Month",
date_trunc('month', rental_date)::date + 19 AS "The 20th of the Month"
FROM rental WHERE customer_id = 459 LIMIT 10;Result:
+---------------------+----------------------+-----------------------+ | rental_date | The 1st of the Month | The 20th of the Month | +---------------------+----------------------+-----------------------+ | 2005-05-24 22:54:33 | 2005-05-01 | 2005-05-20 | | 2005-06-17 02:50:51 | 2005-06-01 | 2005-06-20 | | 2005-06-17 09:38:22 | 2005-06-01 | 2005-06-20 | | 2005-06-17 16:40:33 | 2005-06-01 | 2005-06-20 | | 2005-06-20 02:39:21 | 2005-06-01 | 2005-06-20 | | 2005-06-20 12:35:44 | 2005-06-01 | 2005-06-20 | | 2005-06-20 12:42:00 | 2005-06-01 | 2005-06-20 | | 2005-06-21 02:39:44 | 2005-06-01 | 2005-06-20 | | 2005-07-06 00:22:29 | 2005-07-01 | 2005-07-20 | | 2005-07-08 02:51:23 | 2005-07-01 | 2005-07-20 | +---------------------+----------------------+-----------------------+
Sumber : https://database.guide/get-the-first-day-of-the-month-in-postgresql/
