The sequence of monthly identity dates on the same day or last day of the month in case of invalidity

Question

Given the start date, I want to generate a sequence of dates at monthly intervals, ensuring that each item has the same day as the start date or last day of the month if the same day results in an invalid date.

Sounds pretty standard, right?

Using difftime not possible. Here's what the difftime help file says:

Units such as “months” are not possible because they are not constant lengths. To create intervals of months, quarters, or years, use seq.Date or seq.POSIXt.

But then, looking at the seq.POSIXt help seq.POSIXt , I found that:

Using the “month” first advances the month without changing the day: if this leads to an invalid day of the month, it is calculated forward next month: see examples.

This is an example in the help file.

 seq(ISOdate(2000,1,31), by = "month", length.out = 4) > seq(ISOdate(2000,1,31), by = "month", length.out = 4) [1] "2000-01-31 12:00:00 GMT" "2000-03-02 12:00:00 GMT" "2000-03-31 12:00:00 GMT" "2000-05-01 12:00:00 GMT" 

So, given that the start date is on the 31st day, this will lead to invalid dates in February, April, etc. Thus, the sequence ultimately skips these months because it “counts ahead” and ends on March-02, instead of February-29.

If I start from 2000-01-31, I would like the sequence to be as follows:

• 2000-01-31
• 2000-02- 29
• 2000-03-31
• 2000-04-30
• ...

And he must correctly handle the jumping years, so if the start date is 2015-01-31, the sequence should be:

• 2015-01-31
• 2015-02- 28
• 2015-03-31
• 2015-04-30
• ...

These are just examples illustrating the problem, and I don’t know the start date in advance, and I can’t do anything about it. The initial date may be in the middle of the month (2015-01-15), in which case seq works fine. But it can also be, as in the examples, by the end of the month in dates when using only seq would be problematic (days 29, 30 and 31). I cannot assume that the start date is the last day of the month.

I looked around, trying to find a solution. In some questions here, in SO (for example, here) there is a “trick” to get the last day of the month, getting the first day of the next month and just subtract 1. And finding the first day is “easy” because it is just day 1.

So my solution is:

 # Given an initial date for my sequence initial_date <- as.Date("2015-01-31") # Find the first day of the month library(magrittr) # to use pipes and make the code more readable firs_day_of_month <- initial_date %>% format("%Y-%m") %>% paste0("-01") %>% as.Date() # Generate a sequence from initial date, using seq # This is the sequence that will have incorrect values in months that would # have invalid dates given_dat_seq <- seq(initial_date, by = "month", length.out = 4) # And then generate an auxiliary sequence for the last day of the month # I do this generating a sequence that starts the first day of the # same month as initial date and it goes one month further # (lenght 5 instead of 4) and substract 1 to all the elements last_day_seq <- seq(firs_day_of_month, by = "month", length.out = 5)-1 # And finally, for each pair of elements, I take the min date of both pmin(given_dat_seq, last_day_seq[2:5]) 

It works, but it is, at the same time, a little dumb, hacky and confusing. Therefore, I do not like it. And most importantly, I cannot believe that in R. there is no simpler way to do this.

Can someone point me to a simpler solution? (I think it should have been as simple as seq(initial_date, "month", 4) , but apparently it is not). I searched for it and looked here on the SO and R mailing lists, but apart from the tricks I talked about above, I couldn't find a solution.

Answer 1

The simplest solution is % m +% from lubridate, which solves this exact problem. So:

 seq_monthly <- function(from,length.out) { return(from %m+% months(c(0:(length.out-1)))) } 

Output:

 > seq_monthly(as.Date("2015-01-31"),length.out=4) [1] "2015-01-31" "2015-02-28" "2015-03-31" "2015-04-30" 

Answer 2

Similar to lubridate answer, RcppBDT is used here (which wraps the Boost Date.Time library from C ++)

 R> dt <- new(bdtDt, 2010, 1, 31); for (i in 1:5) { dt$addMonths(i); print(dt) } [1] "2010-02-28" [1] "2010-04-30" [1] "2010-07-31" [1] "2010-11-30" [1] "2011-04-30" R> dt <- new(bdtDt, 2000, 1, 31); for (i in 1:5) { dt$addMonths(i); print(dt) } [1] "2000-02-29" [1] "2000-04-30" [1] "2000-07-31" [1] "2000-11-30" [1] "2001-04-30" R>