SQFlite rawquery to get the closest datetime

Question

I'm programming a birthday app and want to use sqflite to sort the nearest date. Through the page: SQLite query to get the closest datetime I found out about strftime, but unfortunately I don't know how to use it with Flutter. Here are some examples how to sort a date and how I try to find the current date with strftime.

Future>> getSortDateMapListToDate(bool sort) async {
    Database db = await this.database;
    String sortingDirection = '$colDate DESC';
    if(!sort) {
      sortingDirection = '$colDate ASC';
    }

    DateTime test = DateTime.now();
    //var result = await db.query(dateTable, orderBy: sortingDirection);
    var result = await db.rawQuery('SELECT * from $dateTable orderBy abs(strftime("%s",$test) - strftime("%s", $colDate))');
    return result;
  }

Maybe there is another possibility or someone has already worked with rawquerys and strftime.

For any kind of tip or help I would be grateful.

EDIT: With the help from @heitor murara i edit my code:

var result = await db.rawQuery(
        "SELECT s.id, s.name, s.date, s.priority, (strftime('%y-%m-%d', '2020-06-30') - strftime('%y-%m-%d', s.$colDate)) as 'TestDate' "
        "FROM $dateTable s "
        "ORDER BY  TestDate"
    );

the dates i use: 30.06.2020 and with strftime('%s) i get the rows:

2015-08-15 // 32 remaining days
2010-11-03 // 126 remaining days
2010-07-10 // 10 remaining days
1994-07-17 // 17 remaining days
1993-06-29 // 364 remaining days

with strftime('%y-%m-%d) i get the rows:

1994-07-17 // 17 remaining days
1993-06-29 // 364 remaining days
2015-08-15 // 32 remaining days
2010-11-03 // 126 remaining days
2010-07-10 // 10 remaining days

what I am trying to achieve:

2010-07-10 // 10 remaining days
1994-07-17 // 17 remaining days
2015-08-15 // 32 remaining days
2010-11-03 // 126 remaining days
1993-06-29 // 364 remaining days

a other solution that is in my mind, would be a trigger, who "check" every day if the date changes, the remaining days are calculated dynamically.

Has someone a hint? :)

user_12438880 · Answer

The strftime() function you are using do this:

The strftime() routine returns the date formatted according to the format string specified as the first argument.

So, if all your dates were in a format like this: 2020-06-28 you could run a rawQuery simply like this to make the ordering:

SELECT d FROM datetime_text ORDER BY d ASC;

However, if they are not, you can then use the strftime() function to format your date to than order:

SELECT strftime('%Y-%m-%d', d) FROM datetime_text ORDER BY d ASC;

The dates would be returned in something like this:

In your example, I believe you can achieve what you want with something like this:

var result = await db.rawQuery("SELECT strftime('%Y-%m-%d', ) FROM $dateTable ORDERBY $sortingDirection");

As you can see I replaced the * from your query by a just update it with your column name.

Ps.: Since you are working with birthdays, the format '%Y-%m-%d' will do the job, you don't need to use times also.

SQFlite rawquery to get the closest datetime

1 Answers