Using a SQLite date/time field with Flutter and Dart

As a brief note, at the time of this writing, SQLite doesn’t have date/time (datetime) fields, so when you want to use a datetime field with Flutter and Dart, you have to do something else.

Solution: My choice was to store my date/time data as a SQLite INTEGER field. I did this so I can query and sort that field easily myself. The benefit of storing a datetime field as a TEXT field is that you can read it easier, but I think an INTEGER field is a little easier to work with, though YMMV.

My SQLite datetime database solution

Therefore, my solution is to create my SQLite database table like this:

CREATE TABLE quotes (
    id INTEGER PRIMARY KEY,
    quote TEXT UNIQUE,
    author TEXT,
    date_last_touched INTEGER DEFAULT (cast(strftime('%s','now') as int))
)

As shown with that last “date” field, I create it as an INTEGER, and also give it a default timestamp:

date_last_touched INTEGER DEFAULT (cast(strftime('%s','now') as int))

The Flutter/Dart solution

The Flutter/Dart source code solution is to write my Quote data model class like this, with the key being that I define the dateLastTouched field as a Dart integer/int:

class Quote {
    var id = 0;
    var quote = '';
    var author = '';
    var dateLastTouched = 0;   // unix epoch time (because of sqlite)

    Quote (
        this.id, 
        this.quote, 
        [this.author = '', this.dateLastTouched = 0]
    );

    // more code here ...

}

With this “model” class, I then create some Flutter database code like this to create some new records:

db.execute("insert into quotes (quote, author) values ('Be happy in the moment, that’s enough. Each moment is all we need, not more.', 'Mother Teresa')");
db.execute("insert into quotes (quote, author) values ('Be here now.', 'Ram Dass')");

Then I write some SQL INSERT code in Dart like this:

// WARNING: i don’t use this approach; see the following section
final result = await db.rawInsert(
    '''
    INSERT INTO quotes (quote, author)
    VALUES ('${quote.quote}', '${quote.author}')
    '''
);

By skipping the id and date_last_touched fields, I let them use their default values, which makes my Dart code and SQL query simpler.

Important Update #1: Avoiding SQL-injection

As a very important note, I decided not to use raw SQL code as shown above because it creates a potential SQL-injection problem. Therefore, I replaced that INSERT code with this code:

Future<int> insertQuote(Quote quote) async {
    Database db = await instance.database;
    var row = {
        'quote'  : quote.quote,
        'author' : quote.author
    };
    return await db.insert('quotes', row);  //the id
}

Important Update #2: Handling the “update” process

As a result of changing my SQL approach, I also had to create some Dart code to calculate the “seconds since the epoch”:

/// the current time, in “seconds since the epoch”
static int currentTimeInSeconds() {
    var ms = (new DateTime.now()).millisecondsSinceEpoch;
    return (ms / 1000).round();
}

I just tested that function, and it works properly with the date_last_touched database field shown above. I still let my SQL INSERT be handled by the database, but my SQL UPDATE code now looks like this:

/// returns the number of rows update
Future<int> updateQuote(Quote quote) async {
    Database db = await instance.database;

    var currentTimeInSecs = Utils.currentTimeInSeconds();
    var row = {
        'id'     : quote.id,
        'quote'  : quote.quote,
        'author' : quote.author,
        'date_last_touched' : currentTimeInSecs
    };

    // returns num rows updated
    return await db.update(
        'quotes',
        row,
        where: 'id = ?',
        whereArgs: [quote.id]
    );
}

More information

If you wanted to see one way to use a SQLite date/time field with Flutter and Dart, I hope this example has been helpful. For more information, see these SQLite resources:

• https://www.sqlite.org/datatype3.html

That URL has this to say about SQLite Date and Time data types:

SQLite does not have a storage class set aside for storing dates 
and/or times. Instead, the built-in Date And Time Functions of SQLite 
are capable of storing dates and times as TEXT, REAL, or INTEGER values:

- TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
- REAL as Julian day numbers, the number of days since noon in Greenwich 
  on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. 

Applications can chose to store dates and times in any of these formats
and freely convert between formats using the built-in date and time 
functions.

Here’s a link to those SQLite “built-in date and time functions”:

• https://www.sqlite.org/lang_datefunc.html

That page currently states:

SQLite supports five date and time functions as follows:

    date(timestring, modifier, modifier, ...)
    time(timestring, modifier, modifier, ...)
    datetime(timestring, modifier, modifier, ...)
    julianday(timestring, modifier, modifier, ...)
    strftime(format, timestring, modifier, modifier, ...)

The reason I use strftime in my solution is that the strftime documentation specifically states:

The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J.

Again, if you need to use SQLite date and time fields with Flutter, I hope this is helpful.