英文:
To Display the values in the table field Bus-time based on current time in android sqlite
问题
public List<Contact> getBus(String t)
{
List<Contact> contactList = new ArrayList<Contact>();
String query = "SELECT * FROM contacts WHERE CAST( bus_time AS TIME ) >= CAST('" + t + "' AS TIME )";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact();
contact.setID(Integer.parseInt(cursor.getString(0)));
contact.setName(cursor.getString(1));
contact.setPhoneNumber(cursor.getString(2));
// Adding contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}
// return contact list
return contactList;
}
The Error I am getting is
E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.database, PID: 4539
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.database/com.example.database.MainActivity}: android.database.sqlite.SQLiteException: near ":27": syntax error (code 1): , while compiling: SELECT * FROM contacts WHERE CAST( bus_time AS TIME ) >= CAST(18:27 AS TIME )
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2646)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2707)
at android.app.ActivityThread.-wrap12(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1460)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:6077)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)
英文:
My Problem is I need to display all the data in the database based on the field Bus-Time with a comparison with the current time i.e if the current time is 6:30 PM then it should display all the Bus-time field value that is greater than or equal to the current time(6:30).
I had written an SQL query in DatabaseHandler.java in android studio but I am getting error in the query can you help to resolve it out.
The code for the query is.
public List<Contact> getBus(String t)
{
List<Contact> contactList = new ArrayList<Contact>();
String query = "SELECT * FROM contacts WHERE CAST( bus_time AS TIME ) >= CAST(" + t +" AS TIME )";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Contact contact = new Contact();
contact.setID(Integer.parseInt(cursor.getString(0)));
contact.setName(cursor.getString(1));
contact.setPhoneNumber(cursor.getString(2));
// Adding contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}
// return contact list
return contactList;
}
The Error I am getting is
E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.database, PID: 4539
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.database/com.example.database.MainActivity}: android.database.sqlite.SQLiteException: near ":27": syntax error (code 1): , while compiling: SELECT * FROM contacts WHERE CAST( bus_time AS TIME ) >= CAST(18:27 AS TIME )
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2646)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2707)
at android.app.ActivityThread.-wrap12(ActivityThread.java)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1460)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:154)
at android.app.ActivityThread.main(ActivityThread.java:6077)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)
答案1
得分: 0
在SQLite中,没有TIME
数据类型,任何日期和/或时间值都应视为TEXT
。使用函数STRFTIME()
以HH:MM
格式获取当前时间:
SELECT *
FROM contacts
WHERE SUBSTR('0' || bus_time, -5) >= STRFTIME('%H:%M', 'now', 'localtime')
如果您将时间以HH:MM
格式(小时为2位数)保存在列bus_time
中,则不需要SUBSTR()
函数,该函数会在小时只有1位数时在左侧填充0
,代码将为:
WHERE bus_time >= STRFTIME('%H:%M', 'now', 'localtime')
英文:
In SQLite there is no data type TIME
and any date and/or time values should be treated as TEXT
.<br/>
Use the function STRFTIME()
to get the current time in format HH:MM
:
SELECT *
FROM contacts
WHERE SUBSTR('0' || bus_time, -5) >= STRFTIME('%H:%M', 'now', 'localtime')
If you saved the time in the column bus_time
in format HH:MM
(with 2 digits for the hour), you would not need the function SUBSTR()
which pads at the left a 0
if the hour has only 1 digit and the code would be:
WHERE bus_time >= STRFTIME('%H:%M', 'now', 'localtime')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论