Using Sqlite flutter BETWEEN query to return last 2 days records

Issue

I was hoping you guys may be able to help me!

My current code doesn’t work. An error message just gets displayed saying 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 my sqflite query to return the records from the last 2 days. Here’s the query I’m currently using:

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''');
  }

DATABASE STRUCTURE

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

SAMPLE DATA FOR INSERT:

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

Thanks for any help!

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.

Answered By – poolie

Answer Checked By – Jay B. (FlutterFixes Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *