title: Using datetime in SQLite for getting values this month and last month
body: <p>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 <code>lastMonth</code>.</p>
  
  <p>Each transaction amount goes into my table with a transaction date.  I use a SQL query to total it up.  Like so:</p>
  
  <pre><code> if (!lastMonth) {
  
              query += "WHERE ("
                          + "CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 &gt;= CAST(strftime('%s', date('now','start of month')) AS INT)  "
                          + "AND "
                          + " CAST(" + MySQLiteHelper.COLUMN_GAS_UNIX_TIMESTAMP + " AS INT)/1000 &lt; 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) ); ";
              }
  </code></pre>
  
  <p>This <em>mostly</em> 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.</p>
  
  <p><strong>The problem</strong>:  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.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I am wondering if the SQL is correct, but I am putting the dates in database wrong. Here is the code for that:</p>
  
  <pre><code>                Calendar cal = Calendar.getInstance();
                  timestamp = cal.getTimeInMillis();
  </code></pre>