android 如何按保存为TEXT的日期进行筛选？

hivapdat 于 2022-11-20 发布在 Android

关注(0)|答案(3)|浏览(156)

我的SQLite数据库存储发票以及发票编号、创建日期和时间。日期列的类型为TEXT，即YYYY-mm-dd hh: MM: ss (2018-02-12 03:02:59)。
如果用户选择2018-02-01，我希望显示在2018-02-01 00:00:00和当前日期（2017-02-12 23:59:59）之间生成的发票编号。有些人说要更改为UNIX纪元时间格式，但目前还不可能。我应如何创建选择查询以获得所需的结果？
我试探着：

SELECT bill_type, bill_amount, bill_date, bill_person_id, _id, partial_amount, bill_payment_status, bill_payment_date FROM bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and bill_date BETWEEN '2018-02-12 00:00:00 and 2018-02-12 11:59:59' ORDER BY bill_date ASC)

错误：

> FATAL EXCEPTION: AsyncTask #1
>                   Process: com.yourbusinessassistant.stocks, PID: 25275
>                   java.lang.RuntimeException: An error occured while executing doInBackground()
>                       at android.os.AsyncTask$3.done(AsyncTask.java:304)
>                       at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:355)
>                       at java.util.concurrent.FutureTask.setException(FutureTask.java:222)
>                       at java.util.concurrent.FutureTask.run(FutureTask.java:242)
>                       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
>                       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
>                       at java.lang.Thread.run(Thread.java:818)
>                    Caused by: android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): , while compiling: SELECT
> bill_type, bill_amount, bill_date, bill_person_id, _id,
> partial_amount, bill_payment_status, bill_payment_date FROM
> bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and
> bill_date BETWEEN '2018-02-12 00:00:00 and 2018-02-12 11:59:59' ORDER
> BY bill_date ASC
>                   #################################################################
>                   Error Code : 1 (SQLITE_ERROR)
>                   Caused By : SQL(query) error or missing database.
>                       (near "ORDER": syntax error (code 1): , while compiling: SELECT bill_type, bill_amount, bill_date, bill_person_id,
> _id, partial_amount, bill_payment_status, bill_payment_date FROM bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and
> bill_date BETWEEN '2018-02-12 00:00:00 and 2018-02-12 11:59:59' ORDER
> BY bill_date ASC)
>                   #################################################################
>                       at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native
> Method)
>                       at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1093)
>                       at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:670)
>                       at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
>                       at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
>                       at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
>                       at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
>                       at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1454)
>                       at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1301)
>                       at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1172)
>                       at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1340)
>                       at com.yourbusinessassistant.stocks.database.billsdata.BillDataProvider.query(BillDataProvider.java:47)
>                       at android.content.ContentProvider.query(ContentProvider.java:1007)
>                       at android.content.ContentProvider$Transport.query(ContentProvider.java:218)
>                       at android.content.ContentResolver.query(ContentResolver.java:489)
>                       at android.content.CursorLoader.loadInBackground(CursorLoader.java:64)
>                       at android.content.CursorLoader.loadInBackground(CursorLoader.java:42)
>                       at android.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:312)
>                       at android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:69)
>                       at android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:57)
>                       at android.os.AsyncTask$2.call(AsyncTask.java:292)
>                       at java.util.concurrent.FutureTask.run(FutureTask.java:237)

android

来源：https://stackoverflow.com/questions/48744245/how-to-filter-by-dates-stored-as-text

3条答案

按热度按时间

unftdfkk1#

使用此查询：

SELECT * FROM YourTableName WHERE DateTimeColumnName BETWEEN UserInputDate AND DATETIME('NOW', 'LOCALTIME')

这将产生日期-时间介于当前时间和用户输入日期之间所有行LOCALTIME用于补偿您时区，因为SQLite datetime函数使用UTC时区

将查询中的错误添加到问题后编辑：

您使用的查询引发语法错误，因为BETWEEN与AND一起使用，您将AND放在单引号内。请将查询修改为：

SELECT bill_type, bill_amount, bill_date, bill_person_id, _id, partial_amount, bill_payment_status, bill_payment_date FROM bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and bill_date BETWEEN '2018-02-12 00:00:00' AND '2018-02-12 11:59:59' ORDER BY bill_date ASC)

如果要在查询中使用datetime函数，请将其用作：

SELECT bill_type, bill_amount, bill_date, bill_person_id, _id, partial_amount, bill_payment_status, bill_payment_date FROM bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and bill_date BETWEEN '2018-02-12 00:00:00' AND DATETIME('NOW', 'LOCALTIME') ORDER BY bill_date ASC)

赞(0）回复(0）举报 2022-11-20

yzuktlbb2#

试试看：

SELECT bill_type, bill_amount, bill_date, 
bill_person_id, _id, partial_amount, 
bill_payment_status, bill_payment_date FROM 
bill_data_details WHERE bill_type = 1002 and 
bill_person_id = 3 and bill_date BETWEEN
strftime('%Y-%m-%d %H:%M:%S', '2018-02-12 00:00:00') and 
strftime('%Y-%m-%d %H:%M:%S', date('now')) 
ORDER BY bill_date ASC);

赞(0）回复(0）举报 2022-11-20

xxls0lw83#

筛选数据库中以字符串形式存储的日期。查询后，将其转换为“日期格式”，因此可以进行筛选。

Date MyDBdate;
Date Fromdate;

public List<Product> getListProduct(String UserInputFromDate){
    Product product = null;
    List<Product> productList = new ArrayList<>();

    //TODO UserInputFromDate = Fromdate

    openDatabase();
    Cursor cursor = mDatabase.rawQuery("SELECT * FROM Wochenzeiten",null);
    cursor.moveToFirst();

    SimpleDateFormat format;
    String DatumDB;

    while (!cursor.isAfterLast()){

        DatumDB = cursor.getString(1);
        format = new SimpleDateFormat("dd.MM.yyyy");
        try {
            MyDBdate = format.parse(DatumDB);
            System.out.println(MyDBdate);
        } catch (ParseException e) {
            e.printStackTrace();
        }

        try {
            Fromdate = format.parse(UserInputFromDate);
            System.out.println(Fromdate);
        } catch (ParseException e) {
            e.printStackTrace();
        }

        if(MyDBdate.after(Fromdate) || MyDBdate.equals(Fromdate)  ){
            // Erweiterung DatenBank Spalten
            product = new Product(cursor.getInt(0),cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4) );
            productList.add(product);
            cursor.moveToNext();
        }
        else {
            cursor.moveToNext();
        }
    }
    cursor.moveToPrevious();

    // Erweiterung DatenBank hole und setze Werte für MainActivity
    int colID=cursor.getColumnIndex("id");
    int colDatum=cursor.getColumnIndex("Datum");
    int colvon1=cursor.getColumnIndex("von1");
    int colbis1=cursor.getColumnIndex("bis1");
    int colBau1=cursor.getColumnIndex("Bau1");

    if(cursor.getCount() > 0) {
        String sID = cursor.getString(colID);
        String sDatum = cursor.getString(colDatum);
        String svon1 = cursor.getString(colvon1);
        String sbis1 = cursor.getString(colbis1);
        String sBau1 = cursor.getString(colBau1);

        DBculm1 = sID;
        DBculm2 = sDatum;
        DBculm3 = svon1;
        DBculm4 = sbis1;
        DBculm5 = sBau1;
    }

    cursor.close();
    closeDatabase();
    return productList;
}

赞(0）回复(0）举报 2022-11-20

我来回答

android 如何按保存为TEXT的日期进行筛选？

3条答案

相关问题

热门标签

最新问答