To extract the First day of the month for any given date in PostgreSQL, you can use the DATE_TRUNC() function. DATE_TRUNC() function is used to truncate a date or timestamp to a specified level of precision. It allows you to round down a date or timestamp to a specific unit, such as year, quarter, month, week, day, hour, etc . In our case we will get the first day of month for any given date. Also we will populate the first day of the month column based on the date column in postgresql Table.
Syntax for DATE_TRUNC() Function in PostgreSQL:
DATE_TRUNC(unit, source)
unit: Specifies the unit to which you want to truncate the date or timestamp (e.g., ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, etc.).
source: The date or timestamp that you want to truncate.
In Our case. The values of the unit must be “month”.
Extract or Get First day of Month from date in postgresql – DATE_TRUNC() function :
In order to get the First day of month from date in postgresql we will be using DATE_TRUNC() function.
In the below Example we will be passing month as “unit” argument and date as “source” argument in order to extract First day of Month from Date or datetime
SELECT DATE_TRUNC('month', '2024-02-24 15:30:45'::timestamp) AS First_day_of_month;
Output:
Populate First day of month from Date column in postgresql table using DATE_TRUNC() Method 1
We will be using below Student_detail table for our example to depict on how to populate First day of month from date in postgresql
Student_detail:
In the above table we will be using DATE_TRUNC() Function, which will take “month” and column named “birthdaytime” as argument. Which will extract First day of the month based on the “birthdaytime” column and store in the new column of postgresql table as shown below
SELECT *, DATE_TRUNC('month', birthdaytime::timestamp) AS First_day_of_month from Student_detail;
so the resultant table will have First_day_of_month column
