从日期日期从SQL数据库获取数据? [英] Fetching data from sql databse in flutter datewise?

问题描述

我正在开发一个应用程序，并且正在使用sqflite插件实现SQL数据库.在此应用中，用户可以通过填写然后，用户可以验证他们的交易，并且如果他们犯了错误现在，我想添加一个功能，使他们可以在以下屏幕上的它显示为空，但应显示费用的总和

I am making an app in flutter and I am implementing SQL database using the sqflite plugin. In this app, users can create transactions by filling out the form below Then the user can verify their transactions and they also will be having an option to edit the transaction in case they have made a mistake Now I want to add a feature by which they can get the sum of expenses they have done in a particular month on this screen below It is showing null but it should show the sum of the expenses

我查询的代码是

Future getDataJan() async{
final db = await database;
sumJan=await db.rawQuery(
    'SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATE(DATETIME) >= ? AND DATE(DATETIME) <= ?',
    [2021-01-01, 2021-01-31] ).then(Sqflite.firstIntValue);
print(sumJan);
finalJan=sumJan.toString();
}

数据库创建:

Future<Database> createDatabase() async {
String dbPath = await getDatabasesPath();

return await openDatabase(
  join(dbPath, 'expenseDB.db'),
  version: 1,
  onCreate: (Database database, int version) async {
    print("Creating expense table");

    await database.execute(
      "CREATE TABLE $TABLE_EXPENSES ("
      "$COLUMN_ID INTEGER PRIMARY KEY,"
      "$COLUMN_NAME TEXT,"
      "$COLUMN_AMOUNT INTEGER,"
      "$COLUMN_UNNECESSARYEXPENSES INTEGER,"
      "$COLUMN_CATEGORY TEXT,"
      "$COLUMN_DATETIME TEXT"
      ")",
    );
  },
);
}

映射数据库(由于映射时出现字符串，我认为它不起作用)

Mapping Database(I think it's not working because of string while mapping):

Map<String, dynamic> toMap() {
var map = <String, dynamic>{
  DatabaseProvider.COLUMN_NAME: name,
  DatabaseProvider.COLUMN_AMOUNT: amount,
  DatabaseProvider.COLUMN_UNNECESSARYEXPENSES: isUnnecessaryExpenses ? 1 : 0,
  DatabaseProvider.COLUMN_CATEGORY: category,
  DatabaseProvider.COLUMN_DATETIME: pickedDate,
};

if (id != null) {
  map[DatabaseProvider.COLUMN_ID] = id;
}

return map;
}

数据库条目:

这是我的代码，用于创建调用日期选择的小部件:

This is my code for creating a Widget to call for datepicking:

  Widget DatePicker() {

   showDatePicker(context: context, initialDate: DateTime.now(), firstDate: DateTime.parse("2020-01-01 00:00:01Z"), lastDate: DateTime.now()
  ).then((value){
    if (value == null){
      return;
     }
     _dateTime = value;
     ////saving in string
     pickedDate = DateFormat('yyyy-MM-dd').format(_dateTime);
     });
   }

有问题的家伙建议后给出的错误:

Error given after the suggestion by Problematic Dude :

lib/db/database_provider.dart:111:59: Error: Expected ',' before this.
  'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) ==   01').then(Sqflite.firstIntValue);
                                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^
    lib/db/database_provider.dart:111:58: Error: The getter 'm' isn't defined  for the class 'DatabaseProvider'.
    - 'DatabaseProvider' is from 'package:finance_manager/db /database_provider.dart' ('lib/db/database_provider.dart').
    Try correcting the name to the name of an existing getter, or defining a   getter or field named 'm'.
  'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) == 01').then(Sqflite.firstIntValue);
                                                     ^
   lib/db/database_provider.dart:111:57: Error: The operator '%' isn't defined for the class 'String'.
   Try correcting the operator to an existing operator, or defining a '%' operator.
  'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) ==   01').then(Sqflite.firstIntValue);
                                                    ^
  lib/db/database_provider.dart:111:59: Error: The argument type 'String' can't be assigned to the parameter type 'List<dynamic>'.
- 'List' is from 'dart:core'.
     'SELECT SUM(AMOUNT) FROM EXPENSES WHERE strftime('%m' , date(datetime)) == 01').then(Sqflite.firstIntValue);

更新

我在android studio中使用数据库检查器打开了数据库，并尝试在其中运行以下查询:

I opened the database using database inspector in android studio and tried running the following queries in it:

SELECT SUM(AMOUNT) FROM EXPENSES

SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATE(DATETIME) >= 2021-01-01 and  DATE(DATETIME) <= 2021-01-31 

上面的查询工作得很好，并返回了列值的总和，但是，当我运行第二个查询时，它又使我再次为空，我认为我将日期保存为错误的方式.

The above query works perfectly fine and returns the sum of the column amount, but as soon as I run the second query it gives me null again, I think I am saving the date in the wrong manner.

请帮助.

感谢您的回复

推荐答案

您的函数缺少日期前后的引号

Your function is missing quotes around date

Future getDataJan() async{
final db = await database;
sumJan=await db.rawQuery(
    'SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATE(DATETIME) >= ? AND DATE(DATETIME) <= ?',
    ['2021-01-01', '2021-01-31'] ).then(Sqflite.firstIntValue);
print(sumJan);
finalJan=sumJan.toString();
}

这篇关于从日期日期从SQL数据库获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！