Generate Series of Week Intervals for Given Month

Generate series of week intervals for given month

select
    greatest(date_trunc('week', dates.d), date_trunc('month',dates.d)) as start
from generate_series('2013-02-01'::date, '2013-02-28', '1 day') as dates(d)
group by 1
order by 1

Generate series for week number

You can format the date with to show the week number

SELECT to_char(dt, 'iyyy_iw') 
from generate_series ('2017-01-01'::date, '2021-08-01'::date, interval '1 week') as t(dt)

Note that I used the ISO week and year formatting which will yield e.g. 2016-52 for the first date. If you want a non-standard week numbering use the format mask 'yyyy_ww'

Get partial weeks when generating series in postgresql

Faster:

SELECT generate_series(date_trunc('week', '2013-03-01'::date + 6)
                      ,date_trunc('week', '2013-03-14'::date)
                     ,'1 week')::date AS day
UNION  SELECT '2013-03-01'::date
ORDER  BY 1;

For a detailed explanation see related answer:

Generate series of a month by week interval in Postgresql

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.

Using postgres generate_series to generate a recurring schedule

updated answer:

Use generate_series within a case statement to build sequences similarly to ohw I did it in my original answer, with varying frequencies based on the column recurring_schedule.

Specify the date you want the series to generate as an absolute date '2020-01-01'::timestamptz as I've used below, of you can pass a relative date, e.g. NOW() + INTERVAL '10 weeks' instead.

SELECT id event_id, start_at, 
CASE recurring_schedule 
    WHEN 'Weekly' 
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '1 weeks'::INTERVAL)
    WHEN 'Bi-Weekly'
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '2 weeks'::INTERVAL)
    WHEN 'Monthly'
        THEN GENERATE_SERIES(start_at, '2020-01-01'::timestamptz, '1 month'::INTERVAL)
    ELSE NULL 
END recurring_start_time
FROM events;

original answer for schema with json fields:

the syntax for generate_series for datetime types is

generate_series(start_time, end_time, step_interval)

Since your schedule is in JSON contains the interval, you could construct the query as such, and add more schedule types as required.

WITH test (id, start_at, place_id, recurring_schedule) AS (

VALUES 
(358, '2015-01-23 20:00:00 +0000'::TIMESTAMPTZ, 412, 
'{"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'::JSONB),

(359, '2016-01-22 19:30:00 +1100', 414,
'{"validations":{"day":[1]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(360, '2016-02-01 19:00:00 +1100', 415, 
'{"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(361, '2016-02-01 20:00:00 +0000', 416, 
'{"validations":{"day":[4]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}'),

(362, '2014-02-13 20:00:00 +0000', 417,
'{"validations":{"day":[2]},"rule_type":"IceCube::WeeklyRule","interval":1,"week_start":0}')
)
SELECT id, start_at, place_id, 
CASE recurring_schedule->>'rule_type' 
    WHEN 'IceCube::WeeklyRule' 
        THEN GENERATE_SERIES(start_at, NOW(), (recurring_schedule->>'interval' || ' WEEK')::INTERVAL)
    ELSE NULL 
END recurring_start_time
FROM test;

Create list with first and last day of month for given period

Based on this answer:

select d::date as start_date,(d + '1 month'::interval - '1 day'::interval )::date end_date
from generate_series('2014-01-01'::date, '2014-06-30'::date, '1 month'::interval) d

Generating time series between two dates in PostgreSQL

Can be done without conversion to/from int (but to/from timestamp instead)

SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;

---

Related Topics