我正在制作一个健身应用程序,用户可以使用以下模式记录他们的设置:
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]);
}
1条答案
按热度按时间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');