Filtering - string comparison

[kskarzynski]

It would be really nice to be able to compare string fields using filters. I'm thinking about comparing ISO dates, but there are many other circumstances when this might be needed. Here are my use cases:

• Filter date field by month:
  This is doable by generating all days in month and using IN operator:
  startDate IN ['2023-04-01', '2023-04-02', ..., '2023-04-30']
  But this would be so much easier using string comparison:
  startDate >= '2023-04-01' AND startDate <= '2023-04-30'
  or even:
  startDate '2023-04-01' TO startDate '2023-04-30'

• Filter only future dates
  The above solution won't work here; there's no point in generating an arbitrary number of future dates and using IN operator. Date comparison would be really simple: startDate > '2023-04-04'. Same thing goes for filtering on ISO datetime fields.

Without this feature we're not able to implement user-defined filters our site needs. It's a shame, cause using Meilisearch was a really smooth experience up until this point 😄 really wouldn't want to switch back to Elasticsearch

[irevoire]

Hey, sorry for the inconvenience.
I'll let @gmourier or @macraig handle the product discussion.

In the meantime, even if it's not ideal, maybe you could store your dates as a unit timestamp in meilisearch?

[kskarzynski]

Hi, thanks for a quick reply!

Unfortunately timestamps are not an option, since we're using Strapi with Meilisearch Plugin, so the date format is imposed.

[kskarzynski]

@gmourier is there any workaround for the above issues? Or is storing dates as timestamps the only option to compare them?

[gmourier]

Hey, @kskarzynski 👋 Sorry for my late answer 🙇‍♂️

Yes! Storing dates as timestamps is the only option to compare them for now. Have you found a workaround for the Meilisearch Strapi Plugin?

[lazy-geek]

Hi 👋 if you still have the issue, you can transform the data when creating the index in meilisearch strapi plugin. Ref : https://market.strapi.io/plugins/strapi-plugin-meilisearch#-transform-entries

[kskarzynski]

Hey, yes - I ended up converting these values using transform entries, forgot to mention it here 🙂 we now have a quite nice pipeline which preprocesses our data before putting it into Meilisearch indices. Thanks for the replies though!

[candiduslynx]

Additional context from #284 (reply in thread)

Unfortunately, there are issues with nanosecond precision in the Unix timestamp solution.

To go over this in more details:
Consider 2023-04-28 15:50:34.671067000 (unix: 1682697034671067000).
If I follow the unix conversion and store 1682697034671067000 it'll be unmarshaled as 1682697034671066880 which is not the same value.
The reason for this is usage of float64 underneath.
However, storing and unmarshaling "2023-04-28 15:50:34.671067000" works like a charm.
Even the MarshalJSON func for time.Time (Go) utilizes RCF3390Nano for this specific reason.

[gmourier]

Thank you, @candiduslynx for sharing that here

Is it something critical in your use-case to reach this level of precision in the date values?

[candiduslynx]

@gmourier it it critical, as we provide the ELT tool and aim to provide the data to the destinations as close as possible to the data we received from the source side.
Currently, without support for string comparison, we can't implement the delete-stale destination method, thus leaving the users with the slate data.

[kskarzynski]

I ended up transforming Strapi data do Meilisearch format using a transforming function described in the Transform Entries section here.