Question 89635 in PostgreSQL Question posted in
The official documentation can be found here.

Find the n-th occurance of a day in a month in SQL – Postgresql

i am trying to count the number of a weekday in a month just from a date.
My goal is the expectation column. Any idea how I can achieve it with a SQL script?

enter image description here

Tags:

2

Answers


  1. 0

    It is unclear whether the Weekday column is part your input or just part of your output. But you did say just from a date so I will assume it is output (Why id needs repeating I do not know). You can get the day name from the to_char() function. Then use that in the Window function row_number() over(). (see here, here and here)

    with test(dt) as 
     ( select dt::date   
         from generate_series( date '2020-01-01' 
                             , date '2020-01-22'
                             , interval '1 day'
                             ) gs(dt)  
     ) 
 -- your query starts here. 
select dt    "Date" 
     , wd    "Weekday"
     , (row_number(*) over(partition by wd order by dt))::text || '.' || wd "Expected" 
  from (select  dt, to_char(dt, 'Day') wd 
          from test
       ) sq
 order by dt;

    The CTE is used strictly as a data generator. See demo

    FYI. It is best with an international audience to use the ISO 8601 date format yyyy-mm-dd. It is unambiguous regardless of local conventions. If your days had not exceeded 12 we could not know which format (mm-dd-yyyy or dd-mm-yyyy) you used.

    Login or Signup to reply.
  2. 0

    You can calculate how many full weeks has been passed since the start of the month and add 1 to the result. This is the index number of day of week.

    (extract(day from dt)::int - 1) / 7 + 1

    Full example:

    with test(dt) as
         (select dt::date
          from generate_series(date '2020-01-01'
                   , date '2020-01-22'
                   , interval '1 day'
                   ) gs(dt))
select dt, (extract(day from dt)::int - 1) / 7 + 1 || '. ' || to_char(dt, 'Day')
from test
order by dt
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search