Compare database entries to get only the ones from today

Question

Following up on this question: How to query a SQL Lite database using flutter sqflite

I am storing the time the database entries are added using an int that is calculated:

  static int currentTimeInSeconds() {
    var now = (DateTime.now()).millisecondsSinceEpoch;
    return now;
  }

And getting the sum of the amount Column in my Water database using the following in my database_manager.dart file:

  Future<int> sumItems() async {
    await openDb();
    final sum = await _database.rawQuery("SELECT sum(amount) as sum FROM Water");
    return sum[0]["sum"];
  }

How do I only return todays items in the sumItems() function?

Answer 1

in the where clause you must replace time with your column name in the Table

Future<int> sumItems() async {

      DateTime now = new DateTime.now();
      DateTime today = new DateTime(now.year, now.month, now.day);
      DateTime endtoday = today.add(new Duration(days:1));

      await openDb();
      final sum = await _database.rawQuery("SELECT sum(amount) as sum FROM Water WHERE time >= '" + today.millisecondsSinceEpoch.toString() + "' AND time < '" + endtoday.millisecondsSinceEpoch.toString() + "'");
      return sum[0]["sum"];

    }