Select based on month

[tony123S]

I have this column in database

TextColumn get createdAt => text().named('created_at').nullable()();

where the created_at is this format "2019-07-22T11:42:01.000+08:00".

I would like to write a select query, to retrieve data where month is July and year is 2019.

Future<List<ABC>> getData() {
    return (select(abcs)..where(...)   // where the month of created_at is October and Year is 2019
          ..orderBy([
            (t) =>
                OrderingTerm(expression: t.createdAt, mode: OrderingMode.desc)
          ]))
        .get();
  }

How should I write?

[simolus3]

You could use the builtin date function from sqlite. When using moor 2.1 or later, this would look like:

..where((row) {
  // call the date(createdAt) function in sqlite, which parses the time string
  final sqliteDate = FunctionCallExpression<DateTime, DateTimeType>('date', [row.createdAt]);
  return sqliteDate.year.equals(2019) & sqliteDate.month.equals(7);
})

[tony123S]

You could use the builtin date function from sqlite

Mean create a DATETIME column?

[simolus3]

That would also work, yeah. But you don't need to change the table definition. You can convert the text to a date in sqlite (see https://www.sqlite.org/lang_datefunc.html), so it shouldn't be necessary to change the column definition.

[tony123S]

I read the link you sent, but I still have no idea. How can I use that date function inside moor?

[simolus3]

Moor doesn't have anything builtin that matches the date function directly. However, you can call arbitrary sqlite functions by using a FunctionCallExpression. So, to call DATE(created_at), you can use the code from above. The <DateTime, DateTimeType> means that moor will expect the function to return a date time (which it does). Then, you just need to pass the function name and the parameters.

[tony123S]

I used the above code, but not getting any data. This is the query displayed in log

I/flutter (12004): Moor: Sent SELECT * FROM abcs WHERE (CAST(strftime("%Y", date(created_at), "unixepoch") AS INTEGER)) = ? AND (CAST(strftime("%m", date(created_at), "unixepoch") AS INTEGER)) = ?; with args [2019, 10]

[tony123S]

Is it because of the unixepoch ?

[simolus3]

You're right, it fails because of the "unixepoch". My solution doesn't work, sorry for causing confusion. I just tried this, which generates a correct query:

  final asDate = FunctionCallExpression('date', [row.createdAt]);
  final year = FunctionCallExpression<String, StringType>(
      'strftime', [const Constant<String, StringType>('%Y'), asDate]);
  final month = FunctionCallExpression<String, StringType>(
      'strftime', [const Constant<String, StringType>('%m'), asDate]);

  return year.equals('2019') & month.equals('07');

Note that strftime returns a string and that there's a leading 0 in the month.

[tony123S]

It works !!! Thanks again

[NaarGes]

hello I'm trying to get a stream of record which are in the same day of input (like all records with date of Feb 23, 2020). my table attribute is DateTimeColumn and here is my method

 Stream<List<BloodSugar>> watchBloodSugarsInDate(DateTime dateTime) {

    return (select(bloodSugarsEntity)
        ..where((row) {
          final asDate = FunctionCallExpression('date', [row.date]);
          final year = FunctionCallExpression<String, StringType>(
              'strftime', [const Constant<String, StringType>('%Y'), asDate]);
          final month = FunctionCallExpression<String, StringType>(
              'strftime', [const Constant<String, StringType>('%m'), asDate]);
          final day = FunctionCallExpression<String, StringType>(
            'strftime',  [const Constant<String, StringType>('%d'), asDate]);

          return year.equals(dateTime.year.toString()) & month.equals(dateTime.month.toString()) & day.equals(dateTime.day.toString());
        }))
        .map((bloodSugarsEntity) => convertEntityToModel(bloodSugarsEntity))
        .watch();
  }

but it still doesn't work.

[simolus3]

@NaarGes What column type did you use for BloodSugars.date? If it's a dateTime() column, you can just do

..where((row) {
  final date = row.date;
  return date.year.equals(dateTime.year) & date.month.equals(dateTime.month) & date.day.equals(dateTime.day);
});

Also, can you give more details on "it still doesn't work"? Do you not get the results you expect, is there any exception, etc.

[NaarGes]

I've tried that, but it's not working too. I mean it returns error. my query:

Stream<List<BloodSugar>> watchBloodSugarsInDate2(DateTime dateTime) =>
      (select(bloodSugarsEntity)
            ..where((row) {
              final date = row.date;
              return date.year.equals(dateTime.year) &
                  date.month.equals(dateTime.month) &
                  date.day.equals(dateTime.day);
            }))
          .map((bs) => convertEntityToModel(bs))
          .watch();

and this is the error I see in debug mode:

I've also tried flutter clean

[simolus3]

I just tried to reproduce your problem with this gist, but I don't get any sql errors, it works as intended. Do you have a different table structure?

[NaarGes]

No my date row is the same as yours. Here is my table:

class BloodSugarsEntity extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get value => integer()();
  DateTimeColumn get date => dateTime()();
  TextColumn get type => text()();
}

I also insert data fetched from api server in my table, maybe that makes problem. This is my insert method:

  insertBloodSugarList(List<BloodSugar> bsList) {
    for (BloodSugar bs in bsList) {
      insertBloodSugar(BloodSugarsEntityCompanion(
        id: Value(bs.id),
        value: Value(bs.bloodSugar),
        type: Value(bloodSugarApiMeasureState[bs.measureState]),
        date: Value(bs.measureTime),
      ));
    }
  }

bs.measureTime type is DateTime and decodes using this: measureTime: DateTime.parse(json['measure_time']),

json['measure_time'] is in this format for example: "2020-01-11T21:46:25Z"

[simolus3]

Sorry, still no luck. I've updated my repro here - that should be very close to what you're using, right?

[NaarGes]

Yes, That's just like my code. I guess the error is where I'm trying to get date.year I can use date.isBetweenValues and it works alright but when I try to get year out of date it throws no such column: %Y, SQL logic error Thank you anyway

[simolus3]

You could try enabling logStatements on VmDatabase or FlutterQueryExecutor - it will print the full sql statement before running it. It will be easier to debug this for me when I have the full statement.

And just to be sure, are you on the latest moor version (2.4.0)?

[NaarGes]

Yes I'm using latest version on moor and moor_ffi I've enabled logStatement and here's the log: I/flutter ( 3815): Moor: Sent SELECT * FROM blood_sugars_entity WHERE (CAST(strftime("%Y", date, "unixepoch") AS INTEGER)) = ? AND (CAST(strftime("%m", date, "unixepoch") AS INTEGER)) = ? AND (CAST(strftime("%d", date, "unixepoch") AS INTEGER)) = ?; with args [2020, 2, 25]

[simolus3]

Thanks, this helps a lot!

I just published moor version 2.4.1, which should fix this problem.

[NaarGes]

Thank you for your very good support :+1: It's now working :)

[richard457]

how to implement something like this select * from hockey_stats where game_date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00' order by game_date desc;

[simolus3]

@richard457 If you're using a DateTime column, something like this should work:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

[AabhasS]

@richard457 If you're using a DateTime column, something like this should work:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

What if we have a TextColumn for date?

[simolus3]

You could use a FunctionCallExpression for this purpose. If the format you're storing your dates in is one of those time strings, the following should work:

extension MyDateTimeExtension on Expression<String, StringType> {
  Expression<DateTime, DateTimeType> asDateTime() {
    return FunctionCallExpression('strftime', [Constant<String, StringType>('%s'), this]);
  }
}

You can then use stringColumn.asDateTime() just like dateTimeColumn.

[AabhasS]

You could use a FunctionCallExpression for this purpose. If the format you're storing your dates in is one of those time strings, the following should work:

extension MyDateTimeExtension on Expression<String, StringType> {
  Expression<DateTime, DateTimeType> asDateTime() {
    return FunctionCallExpression('strftime', [Constant<String, StringType>('%s'), this]);
  }
}

You can then use stringColumn.asDateTime() just like dateTimeColumn.

It works when: Expression<String,StringType> date = Constant<String,StringType>("2020-02-01 00:00:00.000"); row.gameDate.isBiggerThan(date.asDateTime()); But not when I do : date = DateTime.parse("2020-02-01 00:00:00.000") row.gameDate.isBiggerThan(date);

Thanks for the help :)

[simolus3]

row.gameDate.isBiggerThan(date);

You would have to use isBiggerThanValue(date) here since we can't overload methods in Dart.

[mad0309]

How could i do if i want to get a column null, for example something like this:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...) | row.gameDate.equals(null))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

i put equals null but it doesn't return me anything.

[mad0309]

How could i do if i want to get a column null, for example something like this:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...) | row.gameDate.equals(null))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

i put equals null but it doesn't return me anything.

Solution:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...) | isNull(row.gameDate))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

https://github.com/simolus3/moor/issues/65

[tony123S]

How could i do if i want to get a column null, for example something like this:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...) | row.gameDate.equals(null))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

i put equals null but it doesn't return me anything.

Solution:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...) | isNull(row.gameDate))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

https://github.com/simolus3/moor/issues/65

Are you pasting the correct link?

[mad0309]

How could i do if i want to get a column null, for example something like this:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...) | row.gameDate.equals(null))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

i put equals null but it doesn't return me anything.

Solution:

select(hockeyStats)
  ..where((row) => row.gameDate.isBetweenValues(..., ...) | isNull(row.gameDate))
  ..orderBy([(row) => OrderingTerm.desc(row.gameDate)])

https://github.com/simolus3/moor/issues/65

Are you pasting the correct link?

Sorry this is the correct link https://github.com/simolus3/moor/issues/65#issuecomment-509175940

[tony123S]

@simolus3 Not sure why it doesn't work anymore.

I storing updatedAt as a String

2021-02-22T01:18:22.000+08:00

Here my select query

Future<List<OrganizationSensorReading>> selectReadingBasedOnMonth(
      String months, int years, int organId) {
    return (select(organizationSensorReadings)
          ..where((t) {
            final asDate = FunctionCallExpression('date', [t.updatedAt]);
            final year = FunctionCallExpression(
                'strftime', [const Constant('%Y'), asDate]);
            final month = FunctionCallExpression(
                'strftime', [const Constant('%m'), asDate]);
            return year.equals(years.toString()) &
                month.equals(months) &
                t.organizationSensorId.equals(organId);
          }))
        .get();
  }

Here the version I use

moor_flutter: ^3.1.0
  moor: ^3.4.0

Error

Bad state: Too many elements

[tony123S]

fixed https://github.com/simolus3/moor/issues/1063