Using datetime in SQLite for getting values this month and last month

Question

I have an app which tracks money spent. I have a function that gets all money spent for this month and last month. It is one or the other, not both. It is based on the parameter below called lastMonth.

Each transaction amount goes into my table with a transaction date. I use a SQL query to total it up. Like so:

 if (!lastMonth) {

            query += "WHERE ("
                        + "CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 >= CAST(strftime('%s', date('now','start of month')) AS INT)  "
                        + "AND "
                        + " CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 < CAST(strftime('%s', date('now','start of month','+1 month')) AS INT) ); ";

            } else {

                query += "WHERE ("
                        + "CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 BETWEEN CAST(strftime('%s', datetime('now','start of month', '-1 month')) AS INT)  "
                        + "AND CAST(strftime('%s', date('now','start of month')) AS INT) ); ";
            }

This mostly works. But it seems to have trouble on the last day of the month. For example, this question is being posted in August. So the app dashboard currently shows all money spent in August, and July.

The problem: All July 31st transactions (or whatever the last day of the previous month was) is going in the August total. The July total does NOT include July 31st.

EDIT

I am wondering if the SQL is correct, but I am putting the dates in database wrong. Here is the code for that:

                Calendar cal = Calendar.getInstance();
                timestamp = cal.getTimeInMillis();

Answer 1

It is the use of BETWEEN that is making your LastMonth where statement inclusive of the more than 1 month. BETWEEN is the same as >= AND <=. So the issue when you say.

COLUMN_GAS_UNIX_TIMESTAMP >= [Start of Last Month]
AND COLUMN_GAS_UNIX_TIMESTAMP <= [Start of This Month] 

Is that dates from more than 1 month are returned. All of Last Month and First Day of this month.

Another thought on your current month query, can you assume that the COLUMN_GAS_UNIX_TIMESTAMP will never be greater than NOW? If so you don't need to do anything but check if the COLUMN_GAS_UNIX_TIMESTAMP is >= Start of Month.

You could try something like the follows:

 if (!lastMonth) {

            query += "WHERE ("
                        + "CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 >= CAST(strftime('%s', date('now','start of month')) AS INT)  ";
            } else {

                query += "WHERE ("
                            + "CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 >= CAST(strftime('%s', datetime('now','start of month', '-1 month')) AS INT)  "
                            + "AND "
                            + " CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 < CAST(strftime('%s', date('now','start of month')) AS INT) ); ";
            }

Answer 2

Computing the last day of the month is not the only issue with date/datetime values generally.

If you have a value on the last day of the month that was posted at noon. It will not be counted if you compare just against the date without the date time.

You should do calculations like this where >= '01/01/2015 00:00:00 and where date < 02/01/2015 00:00:00. That way all of the 1/31/2015 records are counted no matter what time they are time stamped at. Or you can strip out the Month, day and year ignoring the time '20150101' and '20150131' as character strings on your comparisons.