generate series based on particular day in each month -postgresql

excel formula for 15th day of month
automatic rolling months in excel
excel if(month is january)
how to add months in excel automatically
month and day in excel
month formula in excel
if(month then)

i have following query in postgresql for dates between 2 ranges.

select generate_series('2019-04-01'::timestamp, '2020-03-31', '1 month') 
         as g_date 

I need to generate specific date in every month .i.e 15 th of every month. Following is my query to generate series

 DO $$

    compdate date = '2019-04-15';
      select  *,
                        when extract('day' from d)  <> extract('day' from compdate)  then 0 
                        when ( extract('month' from d)::int  - extract('month' from compdate)::int ) % 1 = 0 then 1
                        else 0 
                        end as c
          from   generate_series('2019-04-01'::timestamp, '2020-03-31', '1 day') d;

END $$;

SELECT * FROM tmp_table
where c=1;

But every thing is perfect if input date between (1..29)-04-2019 ..


but if i give compdate: 31-04-2019 or 30-04-2019 giving out put:


Expected Output:

 date        flag
2019-04-01     0   ----start_date
2019-04-30     1 
2019-05-31     1
2019-06-30     1
2019-07-31     1
2019-08-31     1
2019-09-30     1
2019-10-31     1
2019-11-30     1
2019-12-31     1
2020-01-31     1
2020-02-29     1
2020-03-31     0  ---end_date

If matched day not found in the result it should take last day of that month..i.e if 31 not found in month of feb it should take 29-02-2019 and also in april month instead of 31 it should take 2019-04-30. Please suggest.

You cannot accomplish what you want with generate_series. This results due to that process applying a fixed increment from the previous generated value. Your case 1 month. Now Postgres will successfully compute correct end-of-month date from 1 month to the next. So for example 1month from 31-Jan yields 28-Feb (or 29), because 31-Feb would be an invalid date, Postgres handles it. However, that same interval from 28-Feb gives the valid date 28-Mar so no end-of-month adjustment is needed. Generate_Series will return 28th of the month from then on. The same applies to 30 vs. 31 day months. But you can achieve what your after with a recursive CTE by employing a varying interval to the same initial start date. If the resulting date is invalid for date the necessary end-of-month adjustment will be made. The following does that:

create or replace function constant_monthly_date
                         ( start_date timestamp
                         , end_date   timestamp
  returns setof date 
  language sql strict
as $$
with recursive date_set as
     (select start_date ds, start_date sd, end_date ed, 1 cnt
      union all
      select (sd + cnt*interval '1 month')  ds, sd, ed, cnt+1 
       from date_set
      where ds<end_date
select ds::date from date_set;    

-- test
select * from constant_monthly_date(date '2020-01-15', date '2020-12-15' );
select * from constant_monthly_date(date '2020-01-31', date '2020-12-31' );

How to generate a date list using the SEQUENCE function, format the cells, and generate a date list quickly with AutoFill or specific functions. Let's see how the SEQUENCE function can help generate a date list Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. The formula generates first day of each month in 2019. To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions. How the formula works In the example, B6

to generate the last days of the month, just generate first days & subtract a 1 day interval

example: the following generates all last day of month in the year 2010

SELECT x - interval '1 day' FROM
GENERATE_SERIES('2010-02-01', '2011-01-01', interval '1 month') x

Generate Sequence of Dates and Time, Create a sequence of datetime values spaced one fixed-length day apart, each datetime must occur at the beginning a month, on a particular day of the week,� If you want a Monday date each week, then pick a Monday date for your start date. If you want Friday dates, pick a Friday start date. Leaving the Step value to 1 and changing the Date Unit to Month will generate, you guessed it, a series of monthly dates. Your starting date will determine which day is being replicated.

Use the least function to get the least one between the computed day and end of month.

create or replace function test1(day int) returns table (t timestamptz) as $$
        select least(date_trunc('day', t) + make_interval(days => day-1), date_trunc('day', t) + interval '1 month' - interval '1 day') from generate_series('2019-04-01', '2020-03-31', interval '1 month') t
$$ language sql;

select test1(31);

How to generate date series to occupy absent dates in google , You can use below to generate on fly all dates in given range (in below example If you want the number of days per month (28--31) to be right, you basically have with numbers 1--31 and remove numbers based on the month and the year. Perhaps I need to write a little function to generate the mask parameters for an arbitrary day of year. – marathonman4202 May 23 '15 at 16:55 Thanks @JohnGalt. This does work, and it is easy enough to create a mask with something like datetime.datetime(year, 1, 1) + datetime.timedelta(days - 1) (see post here ) – marathonman4202 May 23 '15

Generating Rows by Month for Date Ranges in Power Query, However this works at the day-level, and Power Query has pretty good Month span calculation, based on this post in the Power BI forums — I've as many rows as the Month Span column indicates for that particular record. Dates at Start of Month. Generate a sequence of start-of-month dates beginning with April 1, 2014. Specify 'start' as the second input to dateshift to indicate that all datetime values in the output sequence should fall at the start of a particular unit of time. The third input argument defines the unit of time, in this case, month.

Generate Regular Sequences of Dates, seq.Date {base}, R Documentation The method for seq for objects of class class "Date" representing calendar dates. increment of the sequence. Optional. A character string, containing one of "day" , "week" , "month" , "quarter" or "year" . SAS folks- I’m looking for an easy way to generate a series of SAS dates given the start date and end date in macro variables (daily or monthly is typical). I run into this occasionally, and have not found a solution that always works. Given macro variables START_DATE of 01apr1998 and END_DATE of

Create a monthly date range, Create date ranges using a built-in feature. To build a date range that begins with the first date in a month and ends to quickly create number sequences using the selected cells as a guide to determine the size of the date ranges below. This solves the issue with some months having 30 days and some� Seasonal Dummy Model • Deterministic seasonality a linear function of the dummy variables it s i i t D if t December if t February if t January

  • if input is date '2020-01-15', date '2020-12-30' , i need following records 2020-12-16 to is possible?
  • Yes and not much different. Change the line "select (sd + cntinterval '1 month') ds, sd, ed, cnt+1 " to " select least(sd + cntinterval '1 month',ed ) ds, sd, ed, cnt+1"
  • i have changed ..but Job taking too long. Aborted. msg showing..
  • What do you mean Job taking too long. As a single query I just ran the revised version from (date '1900-01-15',date '2020-12-30') - yes 120 years with. Took less than 1 second. What else ig going on here. BTW you indicated "msg showing.." but didn't show anything.
  • sory ..its wrking now thanks a lot..bistake i have changed to where ds<=end_date..
  • thanks but expected result not always month end,,it may 1 to 31st depending on month..i.e 31st not there in feb on that case only take month end otherwise same day of every month
  • Edited the question pls check
  • @Ljt, upon re-reading your question, it is not clear to me what you want. do you want an interface that accept invalid dates and generates a series with a monthly interval? How do you want to handle cases if someone enters 13 as month?