I have two functions where the first one is supposed to return the records for this month and the second one is suppsoed to return the records from last month, but they are both returning the records for this month.
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 BETWEEN datetime('now', 'start of month', 'start of day') AND datetime('now', 'localtime');";
return db.rawQuery(query);
}
static Future<List<Map<String, dynamic>>> getLastMonthtems() async {
final db = await SQLHelper.db(); //get connection
String query =
"SELECT * FROM sets WHERE date BETWEEN datetime('now','start of month','-1 month') AND datetime('now','localtime', 'start of month');";
return db.rawQuery(query);
}
And here is the schema:
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)""");
print('Database created');
}
I was expecting the first function to return sets from this month and the second function to return sets from the last month up until the start of this month. Also, sets from the first day of the month are not getting returning in either function.
Method to insert record into database:
//method to create an item to insert into the database table
static Future<int> createItem(
String exercise_name,
String? total_weight,
String? total_reps,
) async {
DateTime now = DateTime.now();
String formattedDate = DateFormat('yyyy-MM-dd').format(now);
final db = await SQLHelper.db(); //opening the database
final data = {
'exercise_name': exercise_name,
'total_weight': total_weight,
'total_reps': total_reps,
'date': formattedDate,
'is_favorite': 0
}; //making map to insert
final id = await db.insert(
//inserting that data map
'sets',
data, //items is table name and data is the map we made
conflictAlgorithm: sql
.ConflictAlgorithm.replace); //best practice to prevent duplicates
return id;
}
2
Answers
This is the original question of the OP:
Welcome to StackOverflow. Did you do you a SELECT query to first retrieve few records to examine your datetime stamps?
I am also guessing
datecolumn name is a keyword in SQLLite. You may want to check it and change the column name.Here’s what your current DATE BETWEEN clauses in the query produce in terms of dates format. I used a SQLFiddle with sample data with SQLLite(SQL.js) version. Click here to see the SQLFiddle. (Next time you can use that to create, insert your sample tables and your query.)
Funny enough,
dateis a reserved word/keyword in sqllite too. See this postOther part of the OP’s question:
Here’s the update after much of comments:
/*update to to include last day of the last month in the select query: 'now','start of month','-1 day*/`Final two cents: Please keep the dates consistent and always refer to the definitions of the SQL platform you use for your schema. Show a sample of your table data and output results and expected data.
question isn’t super clear, but i’ll try to help.
SQLite does not have an official datetime type. Instead, it stores dates and times as TEXT, REAL or INTEGER values using Date and Time Functions.
As far as i can see your schema is using TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
Let’s use the following data as a test.
Executing the following raw SQL commands should return desired data.
LAST MONTH
CURRENT MONTH
CONSCLUSION
With static values is obvious, now you can try to execute above statement in yor code to see the outcome. If the outcome is wrong, probably the parameters datetime_start and datetime_end are just wrong. If your application runs under different timezones consider applying a Locale/Globalization based on your language offerings. You can give to the parameters any ISO8601 valid string.
Hope it helps,
Hele.