Android SQLite查询从“时间字符串”列中获得不同的Year值

我有一个SQLite数据库,其列为“ KEY_DATUM”,它是格式为“ YYYY-mm-dd”的字符串。

现在我想知道我输入的年份,因此从数据库中取出不同的年份。 我已经尝试过下面的查询,但是我仍然得到重复的值。

有人发现我的错误吗?

提前致谢!

    public ArrayList<String> getAlleJahreSpinner() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(
            true,           //boolean distinct
            TABLE_BIER,             // String Table
            columns,                // String[] columns
            "strftime('%Y'," + KEY_DATUM + ")",  // String selection
            null,       // String[] selectionargs
            KEY_DATUM,              // String groupBy
            null,           // String having
            KEY_DATUM,              // String orderBy
            null);              // String limit
    ArrayList<String> alleJahre = new ArrayList<>();
    // add an "all"-element to the list as first entry
    alleJahre.add("gesamt");

    //iterate through cursor and get distinct year values
    cursor.moveToFirst();
    while (cursor.moveToNext()){
        String zs = cursor.getString(2);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern( "yyyy-MM-dd" );
        LocalDate localDate = LocalDate.parse( zs , formatter );
        alleJahre.add(""+localDate.getYear());
        Log.d("zeit",zs);
    }
    return alleJahre;
    }
评论
  • 续写呐份情
    续写呐份情 回复

    This is a very simple sql statement that you can execute with the method rawQuery():

    public ArrayList<String> getAlleJahreSpinner() {
        SQLiteDatabase db = this.getReadableDatabase();
        String sql = "SELECT DISTINCT strftime('%Y'," + KEY_DATUM + ") FROM " + TABLE_BIER;
        Cursor cursor = db.rawQuery(sql, null);
        ArrayList<String> alleJahre = new ArrayList<>();
        alleJahre.add("gesamt");
        while (cursor.moveToNext()){
            String zs = cursor.getString(0);
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern( "yyyy-MM-dd" );
            LocalDate localDate = LocalDate.parse( zs , formatter );
            alleJahre.add(""+localDate.getYear());
            Log.d("zeit",zs);
        }
        cursor.close();
        db.close();
        return alleJahre;
    }