CODEWITHSUNDEEP
javaandroidsqlitedatetimeandroid-sqlite
Ahtisham
Ahtisham

Reputation: 10116

SQLite filter records by part of column value

Background:

Following are the tables that I have:

Persons table:

Id      Name
-------------

1       user1
2       user2

Orders table:

Id      Name        Date                        YearMonth       UserId
----------------------------------------------------------------------

1       item1       2015-11-01 02:34:46         2015-11         2
2       item2       2018-09-06 01:04:16         2018-09         1
3       item3       2018-09-23 04:44:21         2018-09         1
4       item4       2018-09-02 11:10:08         2018-09         2
5       item5       2019-11-01 02:54:02         2019-11         1

On the UI side I have defined a spinner for each user which is populated with YearMonth column of orders table. For example for user1 the spinner would be populated with:

I use this query to populate the spinner:

 Cursor cursor = db.query("orders",new String[] {"YearMonth"}, "UserId = "+id,
            null, "YearMonth", null, "YearMonth DESC");

// id is a local variable which stores the id of a particular user

And whenever I select any of the YearMonth from the spinner all the orders brought on that year and month get returned with this query:

Cursor cursor = db.query("orders",null,"UserId = ? and YearMonth = ?",
            new String[] {id+"", selectedYearMonth}, null, null, null);

// selectedYearMonth is the value of spinner which is currently selected.

Problem:

If you notice carefully the column YearMonth in orders table is unnecessary I could have done the same with just date column which also mentions the year and month of orders and this is where I want your help.

Can you show me a right way with which I can filter the above queries with part of date column without the need of defining the unnecessary YearMonth column for just filtering the records ?

Upvotes: 0

Views: 989

Answers (3)

forpas
forpas

Reputation: 164089

You can return a column aliased YearMonth from the column Date by using the function SUBSTR(), to get the first 7 chars of the date:

Cursor cursor = db.rawQuery(
    "SELECT SUBSTR(Date, 1, 7) YearMonth FROM orders WHERE UserId = ? ORDER BY YearMonth DESC",  
     new String[] {id + ""}
);

and your 2nd query will be:

Cursor cursor = db.rawQuery(
    "SELECT * FROM orders WHERE UserId = ? AND SUBSTR(Date, 1, 7) = ?", 
    new String[] {id + "", selectedYearMonth}
);

Upvotes: 1

Ahtisham
Ahtisham

Reputation: 10116

Modified the solution of @forpas to best suite the Question:

Modified query for Spinner:

Cursor cursor = db.query("orders", new String[] {"SUBSTR(orderDate, 1, 7) YearMonth"},
                        "userId = ?", new String[] {String.valueOf(personId)},
                        "YearMonth", null, "YearMonth DESC");

Modified query of getting orders:

Cursor cursor = db.query("orders",null,"userId = ? and SUBSTR(orderDate, 1, 7) = ?",
                         new String[] {personId+"", item}, null, null, null);

Upvotes: 0

Viroj Fernando
Viroj Fernando

Reputation: 461

You can query date time with ease, please have a look at this and answer relates to your problem

SELECT
  InvoiceId,
  BillingAddress,
  date(InvoiceDate) InvoiceDate,
Total
FROM
  invoices
WHERE
  InvoiceDate NOT BETWEEN '2009-01-03' AND '2013-12-01'
ORDER BY
  InvoiceDate;

Another example

SELECT ord_num, ord_amount, ord_date, cust_code, agent_code 
FROM orders 
WHERE ord_date NOT BETWEEN '2008-08-02' AND '2008-30-02';

SQLite supports five date and time functions as follows:

  • date(timestring, modifier, modifier, ...)
  • time(timestring, modifier, modifier, ...)
  • datetime(timestring, modifier, modifier, ...)
  • julianday(timestring, modifier, modifier, ...)
  • strftime(format, timestring, modifier, modifier, ...)

better check how you creating the table, your Date column must be a DateTime field. hope this helps. give any feedback so I can help you further.

Upvotes: 0

Related Questions