Getting data with data from a database in Android

Question

In my database, I have 1 table with date as one of the columns that stores the date when the user adds some data to my table.

Now I want to get data only for the current month. I searched through Google, but did not receive a corresponding answer.

Table creation code:

 "create table incomexpense( _id integer primary key autoincrement, "+"price text not null,description text not null, "+"quantity text not null, "+"total text not null, "+"category text not null, "+"recurrence text not null, "+"date text not null, "+"groups text not null, "+" recurrenceid text not null);"; 

Can someone help how to get the 7th day date from the current date.

Answer 1

If you want to "automatically" load the current year / month, use the SQLite strftime function with the modifier 'now' and the format mask '%Y-%m' .

 select strftime('%Y-%m', 'now'); 

Now, coming to your question:

Now I want to get data only for the current month.

Well, this is a mess. You cannot save the date as a datetime type ; instead, it is stored as a string in SQLite format, which it cannot parse using date functions. So you have to convert it to a format that SQLite can understand .

You can do this using the regular substr function

 substr(exp_date, 7) || '-' || substr(exp_date, 4,2) || '-' || substr(exp_date, 1,2) 

Thus, it will be a hidden format dd/mm/yyyy to YYYY-mm-dd .

Thus, the full query will look like this:

 SELECT * FROM incomexpense WHERE Strftime('%Y-%m', 'now') = Strftime('%Y-%m', Substr(exp_date, 7) || '-' || Substr(exp_date, 4, 2) || '-' || Substr(exp_date, 1, 2)) 

See how it works here .

Answer 2

Android does not have a bullet in a data type like Date. You can save it as a string and parse it accordingly when using it. Thus, using this parameter, if you do not extract and analyze the date, you will not be able to find records in the current month.

Another option: you store it as a number - Unix Time, the number of seconds from 1970-01-01 00:00:00 UTC.

With some calculations in the where clause, you can find out the records for the current month.