显示基于当前时间的 Android SQLite 中表字段“Bus-time”的值。

huangapple
go评论168阅读模式
英文:

To Display the values in the table field Bus-time based on current time in android sqlite

问题

  1. public List<Contact> getBus(String t)
  2. {
  3.     List<Contact> contactList = new ArrayList<Contact>();
  4.     String query = "SELECT * FROM contacts WHERE CAST( bus_time AS TIME ) >= CAST('" + t + "' AS TIME )";
  5.     SQLiteDatabase db = this.getWritableDatabase();
  6.     Cursor cursor = db.rawQuery(query, null);
  8.     // looping through all rows and adding to list
  9.     if (cursor.moveToFirst()) {
  10.         do {
  11.             Contact contact = new Contact();
  12.             contact.setID(Integer.parseInt(cursor.getString(0)));
  13.             contact.setName(cursor.getString(1));
  14.             contact.setPhoneNumber(cursor.getString(2));
  15.             // Adding contact to list
  16.             contactList.add(contact);
  17.         } while (cursor.moveToNext());
  18.     }
  20.     // return contact list
  21.     return contactList;
  22. }
  1. The Error I am getting is
  3. E/AndroidRuntime: FATAL EXCEPTION: main
  4. Process: com.example.database, PID: 4539
  5. 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 )
  6.     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2646)
  7.     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2707)
  8.     at android.app.ActivityThread.-wrap12(ActivityThread.java)
  9.     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1460)
  10.     at android.os.Handler.dispatchMessage(Handler.java:102)
  11.     at android.os.Looper.loop(Looper.java:154)
  12.     at android.app.ActivityThread.main(ActivityThread.java:6077)
  13.     at java.lang.reflect.Method.invoke(Native Method)
  14.     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
  15.     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.

  1. public List&lt;Contact&gt; getBus(String t)
  2.     {
  3.         List&lt;Contact&gt; contactList = new ArrayList&lt;Contact&gt;();
  4.         String query = &quot;SELECT * FROM contacts WHERE CAST( bus_time AS TIME ) &gt;= CAST(&quot; + t +&quot; AS TIME )&quot;;
  5.         SQLiteDatabase db = this.getWritableDatabase();
  6.         Cursor cursor = db.rawQuery(query, null);
  7.         
  9.         // looping through all rows and adding to list
  10.         if (cursor.moveToFirst()) {
  11.             do {
  12.                 Contact contact = new Contact();
  13.                 contact.setID(Integer.parseInt(cursor.getString(0)));
  14.                 contact.setName(cursor.getString(1));
  15.                 contact.setPhoneNumber(cursor.getString(2));
  16.                 // Adding contact to list
  17.                 contactList.add(contact);
  18.             } while (cursor.moveToNext());
  19.         }
  21.         // return contact list
  22.         return contactList;
  25.     }

The Error I am getting is

  1. E/AndroidRuntime: FATAL EXCEPTION: main
  2.     Process: com.example.database, PID: 4539
  3.     java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.database/com.example.database.MainActivity}: android.database.sqlite.SQLiteException: near &quot;:27&quot;: syntax error (code 1): , while compiling: SELECT * FROM contacts WHERE CAST( bus_time AS TIME ) &gt;= CAST(18:27 AS TIME )
  4.         at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2646)
  5.         at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2707)
  6.         at android.app.ActivityThread.-wrap12(ActivityThread.java)
  7.         at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1460)
  8.         at android.os.Handler.dispatchMessage(Handler.java:102)
  9.         at android.os.Looper.loop(Looper.java:154)
  10.         at android.app.ActivityThread.main(ActivityThread.java:6077)
  11.         at java.lang.reflect.Method.invoke(Native Method)
  12.         at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
  13.         at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)

答案1

得分: 0

在SQLite中，没有TIME数据类型，任何日期和/或时间值都应视为TEXT。使用函数STRFTIME()以HH:MM格式获取当前时间：

  1. SELECT * 
  2. FROM contacts 
  3. WHERE SUBSTR('0' || bus_time, -5) >= STRFTIME('%H:%M', 'now', 'localtime')

如果您将时间以HH:MM格式（小时为2位数）保存在列bus_time中，则不需要SUBSTR()函数，该函数会在小时只有1位数时在左侧填充0，代码将为：

  1. 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:

  1. SELECT * 
  2. FROM contacts 
  3. WHERE SUBSTR(&#39;0&#39; || bus_time, -5) &gt;= STRFTIME(&#39;%H:%M&#39;, &#39;now&#39;, &#39;localtime&#39;)

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:

  1. WHERE bus_time &gt;= STRFTIME(&#39;%H:%M&#39;, &#39;now&#39;, &#39;localtime&#39;)

huangapple
  • 本文由 发表于 2020年8月16日 21:13:49
  • 转载请务必保留本文链接：https://go.coder-hub.com/63437326.html
  • android
  • android-sqlite
  • java
  • sql
  • sqlite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定