Moolah Diaries – Finding Transaction in the Past Month in MySQL

Ultimately the point of Moolah isn’t just to record a bunch of transactions, it’s to provide some insight into how your finances are going. The key question being how much more or less are we spending than we earn? I use a few simple bits of analysis to answer that question in Moolah, the first of which is income vs expenses over the past x months.

The simple way to do that is to show totals based on calendar month (month to date for the current month), but since my salary is paid monthly that doesn’t provide a very useful view of the current month since no income has arrived yet.

I really want to see data for the previous month as a sliding window.  So on the 3rd July the last month would be 4 June to 3 July (inclusive) and on the 25th of July it would be 26 June to 25 July. Given the way salary and bills are paid each month that provides a fairly stable view of income vs expense without fluctuating too much due to the time of the month.

Previously that was easy enough because all the transactions were in memory and we were iterating in JavaScript – flexible but doesn’t scale particularly well. With Moolah 2 we really want to do that in the database and avoid loading all the transactions.

The key bit of sql that makes it possible to achieve this sliding window for previous month is the group by clause:

GROUP BY IF(DAYOFMONTH(date) > DAYOFMONTH(NOW()), 
    EXTRACT(YEAR_MONTH FROM DATE_ADD(date, INTERVAL 1 MONTH)), 
    EXTRACT(YEAR_MONTH FROM date))

There’s nothing particularly magic here – we just decide whether to push a transaction date into the next month based on whether it’s day of month is past the current day of month. The full transaction is in analysisDao.js.

Initially I limited the query to just the last 12 months worth but MySQL can iterate so much faster than JavaScript it can easily run through the full data set covering about 6 years. I probably should put some limit on it but I’m interested in how long it will last.