Mitch's Technical Blog

Adding Months to a Date in Python

August 11, 2018
articles, python, sql (revised August 12, 2018)
permalink

Summary: I built a datetools module with a smart month_add() function. If you want to skip the background story, the code is down below. Requires Python 3.

SQL Support for Adding Months

Adding months to a date in SQL Server with T-SQL is easy. Use DATEADD() with mm as the datepart value. It handles all the details for you.

PostgreSql has similar capabilities. For example:


SELECT TIMESTAMP '2018-03-31' - INTERVAL '1 month' AS NewDate;

       newdate
---------------------
 2018-02-28 00:00:00
(1 row)

(Lack of) Python Support

Python's standard library, wonderful as it is, has no module with such a built-in function. That battery is not included.

Its absence sort of makes sense. The datetime module does have a timedelta function, but it does not have a months parameter. Nor does it have a years parameter. All of its parameters (hours, weeks, days, etc) are of invariant size, which avoid the subtleties of month sizes, leap years, etc.

Implementation

Even though there is at least one popular external library out there (dateutil and its relativedelta function), I decided to build my own function.

Why? I wanted the challenge of writing concise, readable and Pythonic code to accomplish this seemingly simple but somewhat tricky task.

The tricky parts are:

I found it easy to write spaghetti code while solving this problem, using various tracking variables, modulo arithmetic, and so on.

In another attempt, I used recursion and Python's deque (double ended queue) module. It was more complicated than necessary, but still fun.

Below, I present the solution I ultimately arrived at. It steps one month at a time, using a simple list and dict for year boundary checking. It's nice and clean, with no if-then spaghetti. The only setup work involves getting the starting year, month, and day, as well as the step value (+1 for adding months, -1 for subtracting months).

Before returning the final result, it uses the calendar library's monthrange function to get the total days in the resulting month. It compares that with the day number of the starting month. Using the example above, if you deduct one month from March 31, you should land on February 28. If for some reason the desired result is to land on March 3, use fall_back=False.

As a side note, the module includes another function called sql_date, which is mainly a reminder to myself how easy it is to convert a Python datetime type to a sql date...easy, that is, once I recall the name of the strftime function and its associated directives!

Update: After posting this I did some searching (which I intentionally avoided at first) and discovered Dave Webb's clever solution on Stack Overflow. It employs modulo arithmetic, which is what I'd been groping toward but didn't get to. The missing piece which Dave exploits beautifully is the behavior of floor division, which will round 0.25 down to 0 and -0.25 down (i.e. to the lesser value) to -1. I prefer my implementation because it's more straightforward. Out of curiosity, I did some performance analysis. My intuition told me Dave's would be faster because it theoretically ran in constant time, whereas my algorithm depends on how many months are involved. On a very large data set (the same date manipulated 1M times with a random range of -36 to 36 for the months input), Dave's ran about 3x faster than mine. On a much smaller data set (1K manipulations with a -12 to 12 range), it was about 2x as fast. That makes intuitive sense. My algorithm has a loop, but Dave's involves the more expensive modulo and floor division operations. In practical terms, unless a very large data set is involved, either implementation offers very acceptable performance. Within reasonable bounds, they are both essentially O(n), where n is the number of dates being operated on.

Note: This module requires Python 3.


from calendar import monthrange
from datetime import datetime


def sql_date(date_in=datetime.now()):
    """
    >>> sql_date(datetime(2018, 8, 1, 0, 0))
    '2018-08-01'
    >>> sql_date(datetime(2018, 12, 31, 0, 0))
    '2018-12-31'
    """
    return date_in.strftime('%Y-%m-%d')

def month_add(date_in, add, *, fall_back=True):
    """
    >>> month_add(datetime(2018, 1, 1, 0, 0), -1)
    datetime.datetime(2017, 12, 1, 0, 0)
    >>> month_add(datetime(2018, 1, 1, 0, 0), -1)
    datetime.datetime(2017, 12, 1, 0, 0)
    >>> month_add(datetime(2018, 3, 31, 0, 0), -1)
    datetime.datetime(2018, 2, 28, 0, 0)
    >>> month_add(datetime(2018, 3, 31, 0, 0), -1, fall_back=False)
    datetime.datetime(2018, 3, 3, 0, 0)
    >>> month_add(datetime(2017, 8, 29, 0, 0), 12)
    datetime.datetime(2018, 8, 29, 0, 0)
    >>> month_add(datetime(2017, 8, 29, 0, 0), 13)
    datetime.datetime(2018, 9, 29, 0, 0)
    """
    months = [12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1]
    year_delta = {0: -1, 13: 1}

    step = 1
    if add < 0:
        step = -1

    year = date_in.year
    month = date_in.month
    day = date_in.day

    while add != 0:
       month_key = month + step
       month = months[month_key]
       year = year + year_delta.get(month_key, 0)

       add -= step

    # Be sure day value in target month is a valid value.
    # If it is out of bounds, fall back or roll forward depending
    # on value of fall_back argument.
    max_days = monthrange(year, month)[1]
    delta = day - max_days

    if delta > 0 and fall_back:
        day = max_days
    elif delta > 0:
        # We will never roll forward from December, since it has 31 days.
        # Therefore increment month by 1, knowing that year will stay the same.
        month += 1
        day = delta

    return datetime(year, month, day)


if __name__ == '__main__':
    from doctest import testmod
    testmod()


Contact: hello at escapefromsql.net