Populate or GET First day of week from date in PostgreSQL

To extract the First day (beginning day) of week (Monday) 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 week for any given date. Also we will populate the first day of the week, based on the date column in PostgreSQL Table.

Syntax for DATE_TRUNC() Function in PostgreSQL:

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 “week”.

Extract or Get First day of Week (week beginning) from date in postgresql – DATE_TRUNC() function :

In order to get the First day of Week from date (monday) in postgresql we will be using DATE_TRUNC() function.

In the below Example we will be passing week as “unit” argument and date as “source” argument in order to extract First day of Week from Date or datetime

SELECT DATE_TRUNC('week', '2024-02-24 15:30:45'::timestamp) AS First_day_of_Week;

Output:

Populate First day of Week (week beginning) 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 Week (Monday) from date in postgresql

Student_detail:

In the above table we will be using DATE_TRUNC() Function, which will take “week”  and column named “birthdaytime” as argument. Which will extract First day of the week based on the “birthdaytime” column and store in the new column of postgresql table as shown below

SELECT *, DATE_TRUNC('week', birthdaytime::timestamp) AS First_day_of_week from Student_detail;

so the resultant table will have First_day_of_week column