Extract Day of Week from Date Field in Postgresql Assuming Weeks Start on Monday

Extract day of week from date field in PostgreSQL assuming weeks start on Monday

From the manual

isodow

    The day of the week as Monday (1) to Sunday (7)

So, you just need to subtract 1 from that result:

psql (9.6.1)
Type "help" for help.

postgres=> select extract(isodow from date '2016-12-12') - 1;
  ?column?
-----------
         0
(1 row)
postgres=>

Get this week's monday's date in Postgres?

SELECT current_date + cast(abs(extract(dow FROM current_date) - 7) + 1 AS int);

works, although there might be more elegant ways of doing it.

The general idea is to get the current day of the week, dow, subtract 7, and take the abs, which will give you the number of days till the end of the week, and add 1, to get to Monday. This gives you next Monday.

EDIT: having completely misread the question, to get the prior Monday, is much simpler:

SELECT current_date - ((6 + cast(extract(dow FROM current_date) AS int)) % 7)

ie, subtract the current day of the week from today's date (the number of day's past Monday) and add one, to get back to Monday.

Get the number of the weekday from each date in a date list

Get rid of the date keyword it is only needed to introduce a DATE constant. If you already have a DATE value (which your column is) it's not needed:

select extract(dow from d.date_list)
from dates d

How to get week start and end date string in PostgreSQL?

You can use date_trunc('week', ...).

For example:

SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp);
-> 2012-07-23 00:00:00

Then, you can convert this into a date, if you're not interested in a start time.

To get the end date too:

SELECT    date_trunc('week', '2012-07-25 22:24:22'::timestamp)::date
   || ' '
   || (date_trunc('week', '2012-07-25 22:24:22'::timestamp)+ '6 days'::interval)::date;

-> 2012-07-23 2012-07-29

(I've used the default formatting here, you can of course adapt this to use MM/DD/YYYY.)

Note that, if you want to make comparisons on timestamps, instead of using (date_trunc('week', ...) + '6 days'::interval, you might want to add an entire week and use a strict comparison for the end of the week.

This will exclude y timestamps on the last day of the week (since the cut-off time is midnight on the day).

    date_trunc('week', x)::date <= y::timestamp
AND y::timestamp <= (date_trunc('week', x) + '6 days'::interval)::date

This will include them:

    date_trunc('week', x)::date <= y::timestamp
AND y::timestamp < (date_trunc('week', x) + '1 week'::interval)

(That's in the rare cases when you can't use date_trunc on y directly.)

---

If your week starts on a Sunday, replacing date_trunc('week', x)::date with date_trunc('week', x + '1 day'::interval)::date - '1 day'::interval should work.

Generate series of weeks with their week number and year

Your whole issue stems from selecting the incorrect start date. You stated that monday as first day of week,however you start you calendar on 2020-01-01. That is NOT Monday, it is actually Wednesday. Thus your weeks run from Wednesday through Tuesday. That also gives raise to your week 9 issue as both 2020-02-24 and 2020-03-01 are both in ISO week 9. You correct by changing the start date from 2020-01-01 to 2019-12-30 (or programmatically as date_trunc('week',date '2020-01-01'). Also your row_number can be reduces to row_number() over(). So:

with weeks as (
    select generate_series(date_trunc('week',date '2020-01-01') , current_date, '1 week') as week_starting_date
)
select row_number() over () as id
     ,  extract(week from weeks.week_starting_date)         as week_number    -- ISO Week number 
     ,  extract(year from weeks.week_starting_date)         as week_year      -- ISO Year
     ,  weeks.week_starting_date::date                      as week_start_date
     ,  (weeks.week_starting_date + interval '6 day')::date as week_end_date
  from weeks;

See demo here. You may want to look at rows 1, and 53-54. The values for these columns week_number and week-year are correct via ISO 8601 date specification. If these do not work for you then you will likely need to build a user defined calendar table.

How do I find the date of the first day of week given any date and assuming Monday as day 1 in Excel

Try

=GivenDate-WEEKDAY(GivenDate,2)+1

replace GivenDate with a valid date or the cell reference of a valid date, such as 20/10/2019 which shall return 14/10/2019.

SQL query to get week number from date while setting week starting from Sunday

If there isn't, you can add one day:

extract(week from date + interval '1' day) as week_number

---

Related Topics