DART How to map DATETIME into a SQFlite Database

Question

Overview:

App architecture details:

• state management -> provider (not sure this is relevant, but in case you are interested)
• data storage -> SQFlite

Specific issue details:

I have a calendarDay data model, with a property of

  DateTime date;

I know that DateTime is not supported in SQLite (not in SQFlite either) and the recommendation is to use a String or Integer . I am struggling with how to actually do that.

Error I am getting:

flutter: *** WARNING *** Invalid argument 2021-07-01 15:09:11.129598 with type DateTime. Only num, String and Uint8List are supported. See https://github.com/tekartik/sqflite/blob/master/sqflite/doc/supported_types.md for details This will throw an exception in the future. For now it is displayed once per type.

This is my setup:

calendar_day.dart

 class CalendarDay {
  int? id;
  DateTime date; 

   CalendarDay(
       {this.id,
        required this.date});

    // encode to SQLite database

     Map<String, dynamic> toMap() {
     final map = Map<String, dynamic>();
     map['id'] = id;
     map['date'] = date.toIso8601String(); //toString(); this toString did not work //jsonEncode(date) -> SERIALIZE THE ARRAYS INTO JSON strings, this did not work
      return map;
       }

     // decode from SQLite database

       static fromMap(Map map) {
        return CalendarDay(
         id: map['id'],
        date: DateTime.parse(map['date']), // jsonDecode(map['date']));
      }

     }

database_client.dart

   class DatabaseClient {
     Future<Database> initializedDatabase() async {
     WidgetsFlutterBinding.ensureInitialized();
    String path = await getDatabasesPath();
     return openDatabase(
      join(path, 'three_things_database.db'),
       onCreate: (database, version) async {
        
    await database.execute(
      "CREATE TABLE ${Strings.calendarDayDataBase} (id INTEGER PRIMARY KEY, date TEXT)",
    ); },
       version: 1, ); }

     // Create / insert calendarDay
      Future<void> insertCalendarDay(CalendarDay day) async {
      final Database database = await initializedDatabase();
      await database.insert(
      Strings.calendarDayDataBase,
      day.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
        );
      }
    }

I am thinking the problem lies with the toMap() method, since the error notes the DateTime object. But I am a bit stuck and don't really know how to get around this. Any help is greatly appreciated.

Additional things I have tried in toMap() :

I did include the code commented out, but for clarity, I'll post here:

• I tried mapping the DateTime object to a JSONString. This would hopefully be enough for storing the map in SQLite database, but this approach threw the same error.

• Tried mapping to a regular String using date.toString(). This did not work either. Since the recommendation (link above) from the SQFlite folks is to use ISO8601 string, I thought this approach would work.

Related question(s), yet did not solve my question:

• Create DateTime column in SQFlite

Solution

Here's a clear example :

var dt = DateTime.now();

// String
var dtStr = dt.toIso8601String();
dt = DateTime.tryParse(dtStr);
    
// Int
var dtInt = dt.millisecondsSinceEpoch;
dt = DateTime.fromMillisecondsSinceEpoch(dtInt);

Comments

[MickaelHrndz]

Does this answer your question? Create a DATETIME column in SQLite FLutter database?

[valeriana]

hi MickaelHrndz, it does not, I am still not able to use the database, and I don't understand what I am doing wrong. I did see that question before and I posted it above as related but does not answer my question.