如何比较在数据库中存储为字符串的两个日期 [英] how to compare two dates stored as string in database

问题描述

我正在开发一个应用程序，我需要在两个给定日期之间获取一些数据。我的日期作为字符串存储在数据库中。我使用以下方法，但它无法正常工作，

i am developing an app where i need to get some data between two given dates.my dates are stored in database as string. i am using the following method but its not working,

public ArrayList<String> getTestDates(String fromDate,String toDate){
        ArrayList<String> arrTestResults = new ArrayList<String>();
        String selectQuery = "SELECT  TestDate FROM "  + TABLE_RESULTS + " WHERE TestDate BETWEEN " + fromDate +" AND "+ toDate ;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor result = db.rawQuery(selectQuery, null);
        if(result.moveToFirst()){
            do{
                arrTestResults.add(result.getString(result.getColumnIndex(DatabaseHelper.KEY_TESTDATE)));
            } while (result.moveToNext());
        } else {
            return null;
        }
        
        return arrTestResults;

    }

你能告诉我这个方法有什么问题，或者我应该尝试其他的东西......在Advance中感谢。请让我知道我是否不够清楚



以下是数据库架构

can you please tell what is wrong with this method or i should try something else...Thanks in Advance.Please let me know if am not clear enough

following is schema of database

public static final String KEY_TESTID = "TestId";
    public static final String KEY_TESTLOCATIONID = "LocationId";
    public static final String KEY_TESTLOCATIONNAME = "LocationName";
    public static final String KEY_TESTRESULT = "TestResult";
    public static final String KEY_TESTDATE = "TestDate";
    public static final String KEY_TESTTIME = "TestTime";
    static final String TABLE_RESULTS = "table_Results";
    private static final String DATABASE_CREATE_TABLE_RESULTS = "create table table_Results (TestId integer primary key autoincrement, "
            + "LocationId text not null, "
            + "LocationName text not null, "
            + "TestResult integer not null, "
            + "TestDate text not null, "
            + "TestTime text not null);";

推荐答案

简单：不要这样做。



停止sto将数据库中的日期作为字符串。你不能在包含日期的字符串之间进行任何测试或比较，除非你以非常特定的格式存储它们：

Simple: don''t do it.

Stop storing dates in databases as strings. You can''t properly do any testing or comparisons between strings containing dates, unless you have stored them in a very specific format:

yyyy/MM/dd

（带或不带斜线）。如果以任何其他数据格式存储它们，则每次尝试使用它们时，比较将始终涉及从字符串更改为实际日期。要添加到该问题，您必须将它们转换为日期格式，以便以其首选格式将它们呈现给用户。



因此将它们存储为DateTime（或DateTime2，无论哪个），你可以毫无问题地比较它们，并以用户喜欢的任何格式显示它们。

(with or without the slashes). If you store them in any other data format, you comparisons will always involve changing from a string to an actual date every time you try to use them. And to add to that problme, you have to convert them to a date format in order to present them to the user in his preferred format.

So store them as a DateTime (or a DateTime2, whichever) and you cna compare them without problems, and display them in whatever format your user prefers.

这篇关于如何比较在数据库中存储为字符串的两个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！