java - Get All Unix timestamps from SQLite database within current month -

i have table in sqlite database android app in every row has create_date column specified unix timestamp.

i have query trying add where clause to. need compare timestamp value represents month.

here query before changes:

  string query = "select "               + "sum(" + mysqlitehelper.column_gas_charge + ") allcharges "               + "from " + mysqlitehelper.table_gas + " "; 

i want add a:

 "where " + mysqlitehelper.column_gas_create_date + " = (this month)"; 

it last part unsure of. want rows created this calendar month (not past 30 days).

i thought perhaps can current month integer calendar instance (which know how do), maybe use sqlite function can extract month timestamp. realized wouldn't account current year -- same month any year not need.

is there way can this? not familiar sqlite date/time functions.

edit: here exception get. or without `cast~ in 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) allcharges gas (cast(timestamp integer) >= date('now','start of month') cast(timestamp integer) < date('now','start of month','+1 month')) 

sql has bunch of nice date time functions , need read them, solve problem can use next approach

"where ( date(" + mysqlitehelper.column_gas_create_date + ") >= date('now','start of month') , date(" + mysqlitehelper.column_gas_create_date + ") < date('now','start of month','+1 month') )"; 

note: >= , < in comparison

update: column text, need additional steps:

"where ( cast(" + mysqlitehelper.column_gas_create_date + " int)/1000 >= cast(strftime('%s', date('now','start of month')) int) , cast(" + mysqlitehelper.column_gas_create_date + " int)/1000 < cast(strftime('%s', date('now','start of month','+1 month')) int) )"; 

details:

1. you need convert data in column integer first
2. your value milliseconds, sqllite prefer seconds, have divide 1000
3. output of date function string, need cast integer too