SQL or RoR - Finding the interval period for a given start date and frequency

Question

There exists a blog model which has a creation_date, input_frequency, and input_limit. Input frequency can be every day, week, month (ignore number of days within the month but reset on the day of the month the following month), or year (ignore number of days within the year, but reset on the same day the following year). During a given period determined by input_frequency and the creation_date, the user is allowed to post up to the set input_limit.

For example, said the blog has creation_date Jan 6, with an input_frequency of monthly and input_limit of 4.

I would like to know 3 things relative to today's date (say it is Jun 10):

1. What is the start and end date of my current interval? (Example answer: June 6-July 6)
2. How many more days do I have left until my input limit resets? (26 days)
3. How many posts have been made in the current interval (which I can easily answer after I get #1 Posts.where(created_at: start_interval..end_interval)).

The strategy between the day, week, month, and year interval seems like it might have to be different.

For example, with weekly interval, it would seem harder to calculate the interval as I would have to calculate the number of days today is from the creation_date and do modulo and subtract that modulo from the current day to determine the start_interval and add 7 to that to determine the end_interval - is there any library to do everything that I want to do or do I have to come up with all the calculations myself?

Answer 1

The following code answers my 3 basic questions... (though probably not in the most efficient of manners, it is a answer...)

# assumes that date passed will always be later than start_date
def post_interval_start_date(date = Date.today)
    case input_frequency
    when "entire_duration"
        return creation_date
    when "daily"
        return date
    when "weekly"
        return date - ((date-creation_date).to_i%7).days
    when "monthly"
        # weird math here to account for months with different number of dates
        months_apart = (creation_date.year * 12 + creation_date.month) - (date.year * 12 + date.month)
        months_apart = months_apart-1 if start_date.mday > date.mday
        return creation_date+months_apart.months
    when "yearly"
        return Date.new(date.yday < start_date.yday ? date.year-1 : date.year, creation_date.month, creation_date.mday)
    end
end

def post_next_interval_start_date
    case input_frequency
    when "entire_duration"
        return post_interval_start_date
    when "daily"
        return post_interval_start_date+1.day
    when "weekly"
        return post_interval_start_date+1.week
    when "monthly"
        return post_interval_start_date+1.month
    when "yearly"
        return post_interval_start_date+1.year
    end
end

def post_left_this_period
    input_limit - post_posted_in_period
end

def post_posted_in_period
    posts.where('post_date >= ?', post_interval_start_date()).count
end

Which allows me to call:

# answer #1 (i didn't end up needing end date for what i was doing):
blog.post_interval_start_date() 

# answer #2 
(blog.post_next_interval_start_date()-Date.today).to_i

# answer #3
blog.post_posted_in_period