DateTime Field Filtering Issue When Retrieving Previous Day Records

Support
1

We would like to confirm whether the following filter is the correct approach for retrieving all records from the previous day. FirstPublished is datetime field.

Our scheduler runs daily at 5:00 AM UTC. For example, when the scheduler runs on 30 September 2026, we intend to retrieve all content published on 29 September 2026.

We are currently using the following filter:

(data/FirstPublished/iv gt 2026-09-29T00:00:00Z and data/FirstPublished/iv lt 2026-09-30T00:00:00Z)

Could you please confirm whether this is the correct filter to retrieve all records published on 29 September 2026?

We would also like to clarify:

  • Whether this filter is expected to return all records published on 29 September 2026.
  • Whether there are any known limitations or timezone considerations when filtering DateTime fields.
  • Whether we should be using inclusive operators (ge, le) instead of exclusive operators (gt, lt) for this scenario.
  • Is this the recommended approach for retrieving all records from a specific UTC day?
  • We would like to know whether Squidex provides any built-in functionality to filter using the date portion only of a DateTime field. (eg FirstPublished = 2026-09-29)

Our goal is to retrieve all records published on a specific day without having to manually construct DateTime boundaries for that day.

2

I would use greater-equals for the first comparison but in general it is correct.

3

Thanks for the clarification.

We’ll update the query to:

(data/FirstPublished/iv ge 2026-09-29T00:00:00Z and data/FirstPublished/iv lt 2026-09-30T00:00:00Z)

Just to confirm, is this the recommended and preferred approach for retrieving all records published on a specific UTC day?

Also, does Squidex provide any built-in way to perform a date-only comparison on a DateTime field? For example, is there any function or operator that would allow us to compare only the date portion of FirstPublished (e.g., 2026-09-29) without explicitly specifying a start and end DateTime range?

If such functionality exists, could you provide an example? Otherwise, we’ll continue using the range-based approach above.

4

Internally dates and datetimes are always stored as UTC date-times. I guess we could improve the filter system to transform these queries to a range query, but right now it is not possible.