DateTime in SQL (Flutter)

Question

I am making an app with flutter and storing the data in SQL database using sqflite. How can I store the Date and time in which the form submitted or the other information passed
Currently, I am storing Date as a string (TEXT), but while sorting data entered between dates as it is a string it is not working. Is there a method by which I can store Date and Time in DateTime using sqflite? (If yes please describe the method). If I can parse the dates stored in the string to DateTime somehow while retrieving the information that can help as well but I don't know how can I do it?

Edit:

I am using DateTime format MM-DD-YYYY. I want to store the "date" in the database so that when I need to retrieve the data I can use the 'between' SQL query method to retrieve data, for example:

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();

}

I am using the above function for gathering all the expenses done(modified after the answer from "xahid_rocks") in a month and then summing up the expenses and returning that in a display form. This is one of the use of what I want to do.

Edit 2:

Now when I am calling this it is showing null. This is my code for making a database.

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"
      ")",

Thanks for all your answers and replies.

You should store the date in the format `YYYY-MM-DD hh:mm:ss` which is comparable. — forpas, Mar 07 '21 at 08:28
You could also use ```DateTime().millisecondsSinceEpoch``` which returns you an int which is comparable. — LP Square, Mar 07 '21 at 08:37
Sqlite cannot store DateTime objects. You can convert your DateTime to ISO String and save it as a string in sqlite. Then u just parse the string to a DateTime object. — Abdelbaki Boukerche, Mar 07 '21 at 09:13
You can store duratio.toStrin() object for store to sqlite and decode with this extension function https://gist.github.com/hadi-norouzi/8d539847e4cb7049b53c14548e28b299 — Hadi Norouzi, Mar 07 '21 at 10:57
Thank you for all your replies I have modified the question please check once — Sanchay Kasturey, Mar 13 '21 at 14:14

score 0 · Answer 1 · answered Mar 07 '21 at 12:46

0

You can add date function before the key

sumJan= db.rawQuery('SELECT SUM(AMOUNT) FROM EXPENSES WHERE DATE(DATETIME) >= ? AND DATE(DATETIME) <= ?', [2021-01-01, 2021-01-31] ).then(Sqflite.firstIntValue);

answered Mar 07 '21 at 12:46

xahid_rocks

632
8
24

I have updated the question please check once thanks for your reply. – Sanchay Kasturey Mar 13 '21 at 14:06
Can you give some example from your database records of that table?? – xahid_rocks Mar 14 '21 at 04:36
I am new to flutter can you please tell me how can I view that database records thanks – Sanchay Kasturey Mar 14 '21 at 11:39

DateTime in SQL (Flutter)

1 Answers1