• 6
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

Let's say i'm having a database which includes a table like this:
CREATE TABLE tbl_EX (_id TEXT, TIME TEXT);
And then I insert a value like this:

Date currentTime = Calendar.getInstance(Locale.getDefault()).getTime();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
String time = dateFormat.format(currentTime);
ContentValues contentValues = new ContentValues();
contentValues.put("_id", "SomeID");
contentValues.put("TIME", time);
database.insert("tbl_EX", null, contentValues);

After that, I try to query. Without WHERE clause:

database.query("tbl_EX", new String[]{"_id", "TIME"}, null, null, null, null, "TIME");

It retrieved me all records as expected, which are shown in 2 TextView like this:

_id = SomeID | Time = 2019-03-30 15:00:00

BUT, when I make query with this WHERE clause:

database.query("tbl_EX", new String[]{"_id", "TIME"}, "date(TIME) = ?", new String[]{"date('now')"}, null, null, "TIME");

No data found! I even try replacing the part new String[]{"date('now')"} to
new String[]{"date('2019-03-30')"}or
new String[]{"strftime('%Y-%m-%d', 'now')"} or even
new String[]{"'2019-03-30'"}, still no go.

So, did I store the DateTime data in the SQLite database in the right way? And querying it in the right way too??

      • 1
    • You can try to set not equality, but > or < if you are sure you search right strings. Also you can try to print date('2019-03-30') or other sentences in any SQLite browser you know (see sqlitebrowser.org).

When you pass

new String[]{"date('now')"}

as an argument, this is translated to this query:

select _id, TIME from tbl_EX where date(TIME) = 'date('now')'

Can you see the problem?
date('now') is treated as a string argument for the WHERE clause, so your query searches for literals date('now') in the column TIME.
What you should do instead is this:

database.query("tbl_EX", new String[]{"_id", "TIME"}, "date(TIME) = date(?)", new String[]{"now"}, null, null, "TIME");

This way the parameter now will be passed and your query will be:

select _id, TIME from tbl_EX where date(TIME) = date('now')

Similarly when you want to filter for a specific date like 2019-03-30 you must do:

database.query("tbl_EX", new String[]{"_id", "TIME"}, "date(TIME) = ?", new String[]{"2019-03-30"}, null, null, "TIME");

So you pass 2019-03-30 without the single quotes.

Everything you include in the selectionArgs parameter is treated as a String literal and will be actually surrounded by single quotes in the statement that will be executed.

You can read more here.

  • 1
Reply Report