[SOLVED] BETWEEN query to return last 2 days records

BETWEEN query to return last 2 days records

Error :

Unhandled Exception: DatabaseException(unrecognized token: "'1582268587562" (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM my_table WHERE date BETWEEN '1582095787562' AND '1582268587562) sql 'SELECT * FROM my_table WHERE date BETWEEN '1582095787562' AND '1582268587562' args []}

I'm trying to get records from the last 2 days:

Future<List<Map<String, dynamic>>> queryLastTwoDays() async {
    Database db = await instance.database;
    DateTime now = DateTime.now();
    DateTime twoDaysAgoFromNow = now.subtract(Duration(days: 2));
    var today = now.millisecondsSinceEpoch;
    var twoDaysAgo = twoDaysAgoFromNow.millisecondsSinceEpoch;
    return await db.rawQuery('''SELECT * FROM $table WHERE $columnDate BETWEEN '$twoDaysAgo' AND '$today''');
}

Structure :

CREATE TABLE $table (
    $columnId INTEGER PRIMARY KEY,
    $columnName TEXT NOT NULL,
    $columnAge INTEGER NOT NULL,
    $columnColour TEXT NOT NULL,
    $columnDate INTEGER NOT NULL
)

Data :

DatabaseHelper.columnName : 'Breakfast',
DatabaseHelper.columnAge  : 23,
DatabaseHelper.columnColour : 'red',
DatabaseHelper.columnDate : DateTime.now().millisecondsSinceEpoch,

Solution

As Shawn points out in the comment, your generated SQL is missing a closing quote. Look at the error message:

...while compiling: SELECT * FROM my_table 
WHERE date BETWEEN '1582095787562' AND '1582268587562)

There is no closing ' before the parenthesis.

It's coming from this line, I think:

    return await db.rawQuery('''SELECT * FROM $table WHERE $columnDate BETWEEN '$twoDaysAgo' AND '$today''');

You need one more single-quote before the triple-single-quote.