PostgreSQL provides a built-in date function named the EXTRACT() function that extracts a specific field from a date, time, or timestamp. It allows us to pull out any date/time field, such as a day, month, hour, minute, etc., from any specific DateTime. You can use the EXTRACT() function with the “DAY” or “DOW” argument to get the day from the given date.

This write-up demonstrates various examples to explain how to get the day from a date in Postgres.

How to Get or Extract a Day From a Date in Postgres?

Specify the “Day” as the first argument and a specific date, timestamp, or interval as the second argument to the EXTRACT() function to get a day from the given date, timestamp, or interval:

EXTRACT('Day' FROM source_field);

The above syntax extracts the day of a month(1-31) from a given date, timestamp, or interval. However, if you want to extract the day of the week(DOW), then you can specify the “DOW” as the first argument to the EXTRACT() function:

EXTRACT(‘DOW’ FROM source_field);

It will retrieve a value between 0 and 6, where “0” represents “Sunday” and 6 indicates “Saturday”.

Example 1: How to Get a Day From the Current Date?

Specify the “Day” as the first argument and “CURRENT_DATE” as the second argument to get a day from the current date:

SELECT EXTRACT('Day' FROM CURRENT_DATE);

The output shows that it’s the 31st of the current month.

Example 2: How to Get a Day From a Specific Date?

To get a day from a specific date, you need to specify the “Day” as the first argument and the targeted date as the second argument:

SELECT EXTRACT('Day' FROM DATE '2022-12-17');

The output shows that the EXTRACT() function extracts the day from the specified date.

Example 3: How to Get a Day of Week From the Current Date?

Pass the DOW argument to the EXTRACT() function to get a day of the week from the current date:

SELECT EXTRACT('DOW' FROM CURRENT_DATE);

The EXTRACT() function retrieves 2, indicating its second day of the week, i.e., Tuesday.

Example 4: How to Get a Day of Week From a Specific TIMESTAMP?

To get a day from a specific date, you need to specify the “Day” as the first argument and the targeted date as the second argument:

SELECT EXTRACT('DOW' FROM TIMESTAMP '2022-12-17 11:30:30');

This way, you can use the EXTRACT() function to get a day of the week from the given timestamp.

Example 5: How to Get a Day and DOW From the Table’s Data?

We have already created a sample table named “staff_info”, whose data is enlisted in the following snippet:

SELECT * FROM staff_info;

Suppose we want to extract the day and DOW from the “joining_date”; for this purpose, we will use the EXTRACT() function as follows:

SELECT staff_name, joining_date,
EXTRACT('Day' FROM joining_date) As day,
EXTRACT('DOW' FROM joining_date) As DOW
FROM staff_info;

The output signifies that the EXTRACT() function extracts the day and DOW from the selected column.

Conclusion

In PostgreSQL, the extract function extracts a specific field from a date, time, or timestamp. To get a day from a date, specify the “Day” as the first argument and a specific date, timestamp, or interval as the second argument to the EXTRACT(). To extract the day of the week(DOW) from a date, interval, or timestamp, you must specify the “DOW” as the first argument to the EXTRACT() function. This blog post has presented an in-depth overview of how to get a day from a date in Postgres using the EXTRACT() function.