[Bug report] Create Date sql query not working properly

[priyanshu-simformsolutions]

Version

3.6.2

Platforms

Android

Device Model

All

flutter info

[✓] Flutter (Channel stable, 3.22.2, on macOS 14.3.1 23D60 darwin-arm64,
    locale en-IN)
    • Flutter version 3.22.2 on channel stable at
      /Users/{My_Path}/fvm/versions/3.22.2
    • Upstream repository https://github.com/flutter/flutter.git
    • Framework revision 761747bfc5 (6 months ago), 2024-06-05 22:15:13 +0200
    • Engine revision edd8546116
    • Dart version 3.4.3
    • DevTools version 2.34.3

[✓] Android toolchain - develop for Android devices (Android SDK version
    34.0.0)
    • Android SDK at /Users/{My_Path}/Library/Android/sdk
    • Platform android-34, build-tools 34.0.0
    • Java binary at: /Applications/Android
      Studio.app/Contents/jbr/Contents/Home/bin/java
    • Java version OpenJDK Runtime Environment (build
      17.0.10+0-17.0.10b1087.21-11572160)
    • All Android licenses accepted.

[✓] Xcode - develop for iOS and macOS (Xcode 15.3)
    • Xcode at /Applications/Xcode.app/Contents/Developer
    • Build 15E204a
    • CocoaPods version 1.15.2

[✓] Chrome - develop for the web
    • Chrome at /Applications/Google Chrome.app/Contents/MacOS/Google Chrome

[✓] Android Studio (version 2023.3)
    • Android Studio at /Applications/Android Studio.app/Contents
    • Flutter plugin can be installed from:
      🔨 https://plugins.jetbrains.com/plugin/9212-flutter
    • Dart plugin can be installed from:
      🔨 https://plugins.jetbrains.com/plugin/6351-dart
    • Java version OpenJDK Runtime Environment (build
      17.0.10+0-17.0.10b1087.21-11572160)

[✓] Connected device (7 available)
    • Pixel 6a (mobile)               • **********            •
      android-arm64  • Android 14 (API 34)

[✓] Network resources
    • All expected network resources are available.

• No issues found!

How to reproduce?

When filtering images through CustomColumns.base.createDate on IOS it seems to work as expected,
while in Android i am getting image even from 2-3 years old createDate

Steps i have tried:

• I have even tried with same images on both platform, still same issue happens.
• I have read all meta data of an image it has same date in all fields createDate(date_added), date_taken, modified_date etc... ex(image date is of 2022) and still seems to able to pass this filter even in 2024 (i.e.: According to above filter only last one year image should be able to be fetched)
• I have also tried same thing AdvancedCustomFilter, same issue occurs.

There is some issue with create date filtering on Android side query i guess.

Logs

No response

Example code (optional)

PMFilter? get recentFilterOption {
    final prevYearSqlDateTimeStamp = CustomColumns.utils.convertDateTimeToSql(
      DateTime.now().previousYear, // Used below extension method
      isSeconds: Platform.isAndroid,
    );

    return CustomFilter.sql(
      where: '$commonFilterQuery AND '
          '${CustomColumns.base.createDate} >= $prevYearSqlDateTimeStamp',
      orderBy: [OrderByItem.desc(CustomColumns.base.createDate)],
    );
  }

/// Returns [DateTime] with previous year
  DateTime get previousYear => DateTime(
      year - 1, month, day, hour, minute, second, millisecond, microsecond);

Contact

No response

[fluttercandies-dev]

AI Summary: The user reports that filtering assets by createDate works on iOS but not on Android, where assets from several years ago are returned despite a filter for the last year. They suspect an issue with the SQL query generation on Android.

[AlexV525]

Seems no issues on my device.

[priyanshu-simformsolutions]

As you can refer this recording
i have 6 photos 3 of Nov 24, and other 3 of Oct 22

now when applying filter of date_added grater then Dec 2023,
and i still get all the photos

Note: I have printed CreateDate from AssetEntity
This issue only occurs in Android.

Recorder_10122024_123526.mp4

[AlexV525]

Was the query not working as always regardless of the date? Or was the feature not working on this single phone and provide it's model?

[priyanshu-simformsolutions]

Query is not working on android regardless,
it does give error or 0 images when providing wrong query, but when giving this one it does not work.
Feature is not working on any android phones i have tested it on Pixel 6a and Samsung Galaxy M15.

[AlexV525]

It sounds like a device-specific problem.

[priyanshu-simformsolutions]

Hey Alex, by debugging more on this i was able to reproduce it only happens to picture-specific and only in android(any android device),
but it is not rare as i can see many of my images to have this issue (i have almost 400 such images, all from different sources),
i am sharing two images for example in this link,

Download this images by download all option (In three dot menu) and try performing the same, you will be able to reproduce it.

This both images are from Dec 05, 2022. (If you want more such images for testing i can send it to you)

Before filter

Filter

After Filter

[AlexV525]

How do you read the creation date in the grid?

[priyanshu-simformsolutions]

To read the creation date in the grid, I use AssetEntity.createDateTime.

In the ImageItemWidget, I modified the _buildImageWidget() method as follows:

Note: I use an extension method getReadableDateFormat() to format the DateTime as needed.

Widget _buildImageWidget(
    BuildContext context,
    AssetEntity entity,
    ThumbnailOption option,
  ) {
    return Stack(
      children: <Widget>[
        Positioned.fill(
          child: AssetEntityImage(
            entity,
            isOriginal: false,
            thumbnailSize: option.size,
            thumbnailFormat: option.format,
            fit: BoxFit.cover,
          ),
        ),
        PositionedDirectional(
          bottom: 4,
          start: 0,
          end: 0,
          child: Row(
            children: [
              if (entity.isFavorite)
                Container(
                  padding: const EdgeInsets.all(4),
                  decoration: BoxDecoration(
                    color: Theme.of(context).cardColor,
                    shape: BoxShape.circle,
                  ),
                  child: const Icon(
                    Icons.favorite,
                    color: Colors.redAccent,
                    size: 16,
                  ),
                ),
              Expanded(
                child: Row(
                  mainAxisAlignment: MainAxisAlignment.end,
                  children: [
                    if (entity.isLivePhoto)
                      Container(
                        margin: const EdgeInsetsDirectional.only(end: 4),
                        padding: const EdgeInsets.symmetric(
                          horizontal: 3,
                          vertical: 1,
                        ),
                        decoration: BoxDecoration(
                          borderRadius: const BorderRadius.all(
                            Radius.circular(4),
                          ),
                          color: Theme.of(context).cardColor,
                        ),
                        child: const Text(
                          'LIVE',
                          style: TextStyle(
                            color: Colors.black,
                            fontSize: 10,
                            fontWeight: FontWeight.bold,
                            height: 1,
                          ),
                        ),
                      ),
                    Icon(
                      () {
                        switch (entity.type) {
                          case AssetType.other:
                            return Icons.abc;
                          case AssetType.image:
                            return Icons.image;
                          case AssetType.video:
                            return Icons.video_file;
                          case AssetType.audio:
                            return Icons.audiotrack;
                        }
                      }(),
                      color: Colors.white,
                      size: 16,
                    ),
                  ],
                ),
              ),
            ],
          ),
        ),
        Center(
          child: Text(
            entity.createDateTime.getReadableDateFormat(),
            style: const TextStyle(color: Colors.white),
          ),
        ),
      ],
    );
  }

[priyanshu-simformsolutions]

Hi @AlexV525 ,

I hope you're doing well! I just wanted to check in—were you able to reproduce the issue? Please feel free to reach out if you need anything else.

[AlexV525]

No, I'm not actively investigating this. We process issues mainly in our spare time so it may take a while.