How to get SQFlite data date wise in flutter?

Question

I'm creating todo app in flutter. And I need to show Todos date wise. Like all Todos created today should shown under Today, all the tomorrow's Todos should shown under Tomorrow.

I have created my table like this:

database.execute("""
          CREATE TABLE Todotable(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            taskName TEXT NOT NULL,
            taskTag TEXT NOT NULL,
            date TEXT NOT NULL,
            isReminder INTEGER NOT NULL,
            isCompleted INTEGER NOT NULL
          )
     """);

I don't know how to query SQFlite data date wise and format it like Today and Tomorrow. And show in section like today and tomorrow as shown in design.

Thanks for answers:)

Answer 1

DateTime is not supported in Flutter SQLite package. Check the information about supported SQLite types: https://pub.dev/packages/sqflite#supported-sqlite-types

DateTime is not a supported SQLite type. Personally I store them as int (millisSinceEpoch) or string (iso8601)

We can use String or int as suggested from the package developer. To work with a conditional selection of items of day in DB, perhaps a String is more convenient.

Personally, I prefer user formatter to get the search keyword, as shown below:

    DateFormat formater = DateFormat('yyyyMMdd');
    var today = formater.format(DateTime.now());
    var tomorrow = formater.format(DateTime.now().add(const Duration(days: 1)));

To search in DB, I'd use a method like below:

Future<TodoList?> getTodoListByDay(String day) async {
    final db = await database;
    String sql =
        "SELECT * FROM Todotable WHERE date = \'${day}\' ";
    var res = await db.rawQuery(sql);
    List<TodoList> objs = res.isNotEmpty
        ? res.map((c) => TodoList.fromMap(c)).toList()
        : [];

    return objs.isNotEmpty ? objs : null;
}

TodoList class could be generate from json, as I do with 'jsonToDartModel' package.

import 'dart:convert';

class TodoList {
  int? id;
  String? taskName;
  String? taskTag;
  String? date;
  int? isReminder;
  int? isCompleted;

  TodoList({
    this.id,
    this.taskName,
    this.taskTag,
    this.date,
    this.isReminder,
    this.isCompleted,
  });

  factory TodoList.fromMap(Map<String, dynamic> data) => TodoList(
        id: data['id'] as int?,
        taskName: data['taskName'] as String?,
        taskTag: data['taskTag'] as String?,
        date: data['date'] as String?,
        isReminder: data['isReminder'] as int?,
        isCompleted: data['isCompleted'] as int?,
      );

  Map<String, dynamic> toMap() => {
        'id': id,
        'taskName': taskName,
        'taskTag': taskTag,
        'date': date,
        'isReminder': isReminder,
        'isCompleted': isCompleted,
      };

  /// `dart:convert`
  ///
  /// Parses the string and returns the resulting Json object as [TodoList].
  factory TodoList.fromJson(String data) {
    return TodoList.fromMap(json.decode(data) as Map<String, dynamic>);
  }

  /// `dart:convert`
  ///
  /// Converts [TodoList] to a JSON string.
  String toJson() => json.encode(toMap());
}

Answer 2

You can create DATETIME columns in sqflite. Here is an example for a Weather table created in a sqflite database:

batch.execute('''
CREATE TABLE $tableWeather (
$weatherLocalization TEXT NOT NULL,
$weatherDate DATETIME NOT NULL,
$weatherHumidityPercentage REAL,
$weatherWindDegree REAL,
$weatherWindSpeed REAL,
$weatherPressureHPascal INTEGER,
$weatherTemperatureCelsius REAL,
$weatherDescription TEXT,
$weatherIconId TEXT,
PRIMARY KEY($weatherLocalization, $weatherDate));
''',);

You can create a Weather object as follows:

class Weather{
      String localization;
      DateTime date;
      double humidityPercentage;
      double windDegree;
      double windSpeedMS;
      int pressureHPascal;
      double temperatureCelsius;
      String description;
      String iconId;

  Weather({
    @required this.localization,
    @required this.date,
    this.humidityPercentage,
    this.windDegree,
    this.windSpeedMS,
    this.pressureHPascal,
    this.temperatureCelsius,
    this.description,
    this.iconId
  });

  //to be used when inserting a row in the table
  Map<String, dynamic> toMap() {
    final map = new Map<String, dynamic>();
    map["$weatherLocalization"] = localization;
    map["$weatherDate"] = date.toString();
    map["$weatherHumidityPercentage"] = humidityPercentage;
    map["$weatherWindDegree"] = windDegree;
    map["$weatherWindSpeed"] = windSpeedMS;
    map["$weatherPressureHPascal"] = pressureHPascal;
    map["$weatherTemperatureCelsius"] = temperatureCelsius;
    map["$weatherDescription"] = description;
    map["$weatherIconId"] = iconId;
    return map;
  }

  //to be used when converting the row into object
  factory WeatherOnDate.fromMap(Map<String, dynamic> data) => new WeatherOnDate(
      localization: data["$weatherLocalization"],
      date: DateTime.parse(data["$weatherDate"]),
      humidityPercentage: data["$weatherHumidityPercentage"],
      windDegree: data["$weatherWindDegree"],
      windSpeedMS: data["$weatherWindSpeed"],
      pressureHPascal: data["$weatherPressureHPascal"],
      temperatureCelsius: data["$weatherTemperatureCelsius"],
      description: data["$weatherDescription"],
      iconId: data["$weatherIconId"]
  );
}

Be careful to transform your DateTime attribute to a String or int as I did in the toMap() function.

Then, when you want to fetch a date you can do this:

Future<Weather> fetchWeatherOnDate(DateTime dateTime) async {
  DatabaseHelper _databaseHelper = Injection.injector.get();
  List<Map<String, dynamic>> weatherMaps = await _databaseHelper.db.rawQuery(
      'SELECT * FROM $tableWeather WHERE DATE($weatherDate) = DATE(?)',
      [dateTime.toString()]);

  List<Weather> weathers= [];
  for (final weatherMap in weatherMaps) {
    weathers.add(Weather.fromMap(weatherMap));
  }
  if (weathers.isNotEmpty){
    return weathers[0];
  }
  return null;
}

DateTime today = DateTime.now()
Weather weatherToday = fetchWeatherOnDate(today);

I think that it gives you a good idea of how to solve your problem :)

Answer 3

for getting todos for tomorrow

//for tomorrow
           String tomorrowDate=  DateTime.now().add(Duration(days: 1)).toIso8601String();
              var todosForTomrrow= await database
                        .rawQuery('SELECT * FROM Todotable WHERE date = ?', [tomorrowDate]);
    //for today
               String todayDate=  DateTime.now().toIso8601String();
              var todosForToday= await database
                        .rawQuery('SELECT * FROM Todotable WHERE date = ?', [todayDate]);

Date is converted and saved here in string format and date should converted to same format before inserting into the table like this