In PostgreSQL you can use the extract() function to get the day from a date.

You can also use date_part() to do the same thing.

When extracting the day from a date, you need to specify what sense of the word “day” you mean. For example, “day of week”, “day of month”, “day of year”, etc.

Example 1: Day of Week

Here’s an example of using the extract() function to extract the day of the week from a date.

Using dow returns the day of the week as Sunday (0) to Saturday (6).

SELECT 
  extract(dow from date '2020-12-27') AS "Day of week",
  to_char(date '2020-12-27', 'Day') AS "Day Name";

Result:

 Day of week | Day Name  
-------------+-----------
           0 | Sunday   

In this example I also returned the day name, so that there’s no confusion as to which day is actually being returned.

I’ll use the same date for the remaining examples, so there’s no need for me to print out the day name in those examples.

Example 2: ISO Day of Week

Using isodow returns the day of the week as Monday (1) to Sunday (7).

SELECT extract(
    isodow from date '2020-12-27'
    ) AS "ISO Day of week";

Result:

 ISO Day of week 
-----------------
               7

The reason I used Sunday in these examples is because it highlights the difference between isodow and dow.

Example 3: Day of Month

When using timestamp or date values, day returns the day of the month (1 – 31).

SELECT extract(
    day from date '2020-12-27'
    ) AS "Day of month";

Result:

 Day of month 
--------------
           27

When using an interval value, the day field results in the number of days being returned.

SELECT extract(
    day from interval '32 weeks'
    ) AS "Number of days";

Result:

 Number of days 
----------------
            224

Example 4: Day of Year

Using doy returns the day of the year (1 – 365/366).

SELECT extract(
    doy from date '2020-12-27'
    ) AS "Day of year";

Result:

 Day of year 
-------------
         362

Example 5: The date_part() Function

The date_part() function can be used in place of the extract() function. Here’s an example.

SELECT date_part(
    'doy', timestamp '2020-12-27'
    ) AS "Day of year";

Result:

 Day of year 
-------------
         362