Create a DATETIME column in SQLite FLutter database?

Question

I create a TABLE with some columns and everything's ok until I tried to insert a new DATETIME column:

_onCreate(Database db, int version) async {
await db.
execute("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY, $NAME TEXT, $COLOUR 
TEXT, $BREED TEXT, $BIRTH DATETIME)");
}

My Model Class to store is:

class Pet {
 int id;
 String name;
 DateTime birth;
 String breed;
 String colour;

 Pet({this.id, this.name, this.birth, this.colour, this.breed});
 }

In the controller, I tried to store a new instance of Pet, instantiating a new variable DateTime _date = new DateTime.now(); and saving all in

Pet newPet = Pet(
      id: null,
      name: _name,
      colour: _colour,
      breed: _breed,
      birth: _date
  );

But when I insert in the database I receive:

Unhandled Exception: Invalid argument: Instance of 'DateTime'

Why is $ prefexied for table names?

Anoop Thiruonam
– Anoop Thiruonam

2021-03-02 19:25:42 +00:00
Commented Mar 2, 2021 at 19:25 — Anoop Thiruonam
– Anoop Thiruonam, Commented Mar 2, 2021 at 19:25
@AnoopThiruonam to avoid hard-coding the table name.

sm-sayedi
– sm-sayedi

2022-02-16 16:04:03 +00:00
Commented Feb 16, 2022 at 16:04 — sm-sayedi
– sm-sayedi, Commented Feb 16, 2022 at 16:04

Mehmet Esen · Accepted Answer · 2022-07-23 08:45:40Z

39

You are passing DateTime object directly.

Use class methods like:

https://api.dart.dev/stable/2.9.3/dart-core/DateTime/millisecondsSinceEpoch.html

or

https://api.dartlang.org/stable/2.4.0/dart-core/DateTime/toIso8601String.html

You need to pass String or int instead of an Object. The reason being, you must use supported SQLite data types to store in a SQLite table, find a list of supported data types here https://www.sqlite.org/datatype3.html

Also checkout:

https://pub.dev/packages/sqflite

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

To retrieve your DateTime object again:

From Integer:

DateTime.fromMillisecondsSinceEpoch(yourValue);

From String:

DateTime.parse(yourValue);

or better way:

DateTime.tryParse(yourValue);

edited Jul 23, 2022 at 8:45

answered Jul 23, 2019 at 13:33

Mehmet Esen

7,0043 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Athira Reddy Over a year ago

If I store dates as int(millsSinceEpoch), can I retrieve based on dates? for example I want to get data based on dates, so select * from tablename ORDER BY date_int;

Mehmet Esen Over a year ago

Yes @AthiraReddy , DateTime.fromMillisecondsSinceEpoch() constructor will crate new DateTime object from your integer, I edited my answer checkout.

Ali Parsa · Accepted Answer · 2025-04-05 14:53:33Z

Hi (my english is not good), i can solve this problem with sqlite3 package in my windows software (Flutter).

You should use text format for date:

db.execute('''
    CREATE TABLE IF NOT EXISTS database (
      id INTEGER NOT NULL PRIMARY KEY,
      name TEXT NOT NULL,
      value TEXT NOT NULL,
      date TEXT NOT NULL
    );
  ''');

Then use .toString() for DateTime:

final stmt =
        db.prepare('INSERT INTO database (name, value, date) VALUES (?, ?, ?)');
    stmt.execute(
        ['The Beatles', '31', DateTime.now().toString().split(".")[0]]);

Finally use datetime() function to convert String to Date in sqlite:

  final sql3.ResultSet resultSet = db.select(
        "SELECT * FROM database WHERE datetime(date) >= datetime('2025-04-03 00:00:00') AND datetime(date) <= datetime('2025-04-05 00:00:00')");

Collectives™ on Stack Overflow

Create a DATETIME column in SQLite FLutter database?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related