sqlite 我正在特灵从这个月和上个月获得所有数据,以便我可以比较%变化

1cklez4t  于 2023-04-30  发布在  SQLite
关注(0)|答案(1)|浏览(150)

我正在制作一个健身应用程序,用户可以使用以下模式记录他们的设置:

static Future<void> createTables(sql.Database database) async {
    await database.execute("""CREATE TABLE sets(
      id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      exercise_name TEXT,
      total_weight TEXT,
      total_reps TEXT,
      date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      is_favorite INTEGER 
      )
      """);
  }

现在我需要两个函数,我可以使用日期返回当前月份和上个月的数据,但它不起作用:

//this will return a list of maps
//gets called when app is launched and will get all our shit from the database
  static Future<List<Map<String, dynamic>>> getTodayItems() async {
    final db = await SQLHelper.db(); //get connection
    DateTime now = DateTime.now();
    String formattedDate = DateFormat('yyyy-MM-dd').format(now);
    return db.rawQuery('SELECT * FROM sets WHERE date = ?', [formattedDate]);
  }

  static Future<List<Map<String, dynamic>>> getThisMonthItems() async {
    final db = await SQLHelper.db(); //get connection
    DateTime now = DateTime.now();
    String query = "select * from sets where date <= DATE('now', '-1 month')";
return db.rawQuery(query);
  }

  static Future<List<Map<String, dynamic>>> getLastMonthtems() async {
    final db = await SQLHelper.db(); //get connection
    DateTime now = DateTime.now();
    String formattedDate = DateFormat('yyyy-MM-dd').format(now);
    return db.rawQuery('SELECT * FROM sets WHERE date = ?', [formattedDate]);
  }
xlpyo6sf

xlpyo6sf1#

对于当前日期,使用此SQL:
SELECT * FROM sets WHERE date BETWEEN datetime('now', 'start of day') AND datetime('now', 'localtime');
本月:
SELECT * FROM sets WHERE date BETWEEN datetime('now', 'start of month') AND datetime('now', 'localtime');
上个月:
SELECT * FROM sets WHERE date BETWEEN datetime('now','start of month','-1 month') AND datetime('now', 'start of month');

相关问题