-1

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.

EDIT: Here is an Exception I get. I get this with or without the `CAST~ in the SQL.

04-24 13:46:50.412: E/AndroidRuntime(26631): android.database.sqlite.SQLiteException: near "CAST": syntax error (code 1): , while compiling: SELECT SUM(gas) AS allCharges FROM gas WHERE (CAST(timestamp as integer) >= date('now','start of month') CAST(timestamp as integer) < date('now','start of month','+1 month'))
TheLettuceMaster
  • 15,594
  • 48
  • 153
  • 259

1 Answers1

2

sql like has bunch of nice date time functions and you definitely need to read about them, to solve your problem you can use next approach

"WHERE (
date(" + MySQLiteHelper.COLUMN_GAS_CREATE_DATE + ") >= date('now','start of month')
AND
date(" + MySQLiteHelper.COLUMN_GAS_CREATE_DATE + ") < date('now','start of month','+1 month')
)";

NOTE: >= and < in comparison

UPDATE: As soon as column it text, you need to do some additional steps:

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

details:

  1. you need to convert data in your column into integer first
  2. your value is milliseconds, but sqllite prefer seconds, so you have to divide by 1000
  3. output of date function is string, so we need to cast it to integer too
Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
  • I am getting a `SQLiteexception`. I think because my date column is text and not an integer maybe? Does it matter what `date` compares with? going to try to cast and see if that works. EDIT: Cast did not work I added the `exception` above. – TheLettuceMaster Apr 24 '15 at 20:46
  • @KickingLettuce it is better to convert all dates into unixtimestamps, also check http://stackoverflow.com/questions/1975737/sqlite-datetime-comparison – Iłya Bursov Apr 24 '15 at 20:49
  • Well I am storing it directly from a Java `getTimeInMillis()` method I think. So I get the unix time and store it in a text column. Maybe the datatype isn't the issue. I added the query up top. Can you check the syntax maybe? (and picture it without the cast too) – TheLettuceMaster Apr 24 '15 at 20:52
  • @KickingLettuce what is the type of your datetime column? integer or string? – Iłya Bursov Apr 24 '15 at 20:53
  • It is `text not null DEFAULT ''` So that's a String. – TheLettuceMaster Apr 24 '15 at 20:56
  • @KickingLettuce I've updated answer with additional steps, I don't have possibility to verify whether it has typos or not, but you can get idea – Iłya Bursov Apr 24 '15 at 21:02
  • Yes that worked. Turns out the first exception was simply there was no `AND` in the query (see the query above). But once I put the `AND` in it I then got no results. Either because of the datatype but more likely the "/1000". Anyhow. It works now with your updated answer. Thanks again. – TheLettuceMaster Apr 24 '15 at 21:27
  • In case someone still has problems, try using the modifiers `unixepoch` and `localtime`, so the resulting query would look like: `"WHERE ( date(" + MySQLiteHelper.COLUMN_GAS_CREATE_DATE/1000, 'unixepoch', 'localtime' + ") >= date('now','start of month') AND date(" + MySQLiteHelper.COLUMN_GAS_CREATE_DATE /1000, 'unixepoch', 'localtime'+ ") < date('now','start of month','+1 month', '-1 day') )";` – AlvaroSantisteban Sep 17 '19 at 12:13