vendredi 24 avril 2015

Get All Unix timestamps from SQLite database within current month

I have a table in an SQLite database for an Android app in which every row has a create_date column specified as a unix timestamp.

I have a query that I am trying to add a WHERE clause to. I need to compare the timestamp with a value that represents this month.

Here is my Query before the changes:

  String query = "SELECT "
              + "SUM(" + MySQLiteHelper.COLUMN_GAS_CHARGE + ") AS allCharges "
              + "FROM " + MySQLiteHelper.TABLE_GAS + " ";

I want to add a:

 "WHERE " + MySQLiteHelper.COLUMN_GAS_CREATE_DATE + " = (this month)";

It is that last part I am unsure of. I basically want to get all rows created this calendar month (not the past 30 days).

I thought perhaps I can get the current month integer from a Calendar instance (which I know how to do), then maybe use an SQLite function that can extract the month from the timestamp. Then I realized that wouldn't account for the current year only -- it would get the same month for any year which is not what I need.

Is there a way I can do this? I am not too familiar with SQLite date/time functions.

Aucun commentaire:

Enregistrer un commentaire