Date comparison with datetime & date

Help
,
1

Hey all,

How come this query filter works:

“start_time”: {
“_gte”: “{{$trigger.query.date}}”
}

*Where start_time = datetime and date = 2025-07-11

But this doesn’t?

“start_time”: {
“_eq”: “{{$trigger.query.date}}”
}

I wouldn’t expect either to work but I’m only trying to filter start_time’s date == query.date.

Hopefully that makes sense :grimacing:

Any ideas without having to write custom js?

Just guessing, but I assume date type gets casted to datetime resulting in 2025-07-11 00:00:00. That's possibly why "_gte" may yield a result but there may be no records with that exact time when comparing "_eq"

 –

Thanks Nik! That was my suspicion. So how do I filter 'today' without returning future events too? :thinking:

 –

I just realized that all 3 topics i posted in are possibly related to the same question. This https://community.directus.com/t/how-to-filter-today-on-weekday/725/2 didn't solve you problem?

 –

Close, but no. In my project I have events (where date and time is provided) and scheduled_items (where days_of_week is specified) so related but not the same issue. FYI what has worked for me is this: "start_time": { "_between": [ "{{$trigger.query.date}}T00:00:00Z", "{{$trigger.query.date}}T23:59:59Z" ] } Plus it accounts for events that start before midnight but cross into the following day.

 –

I see, and yeah, _between would have been my next shot on this topic too.

 –

1 Answer

1
2

Great question! Yeah, the answers in the comments are spot on.

The datetime field type gets cast to a full date and time value, which is why your equal filter didn’t work as expected. When you’re comparing dates, you’re essentially comparing something like 2023-07-14 00:00:00 against 2023-07-14 15:30:22 - they’ll never be equal.

Solutions

Option 1: Use the date field type
If you only need date comparison without time, use the field type date instead of datetime. This will store just the date portion and make equality comparisons work as expected.

That should allow this syntax to work.

{
	filter: {
		start_time: {
			_eq: "2025-07-14"
		}
	}
}

Option 2: Use the between filter (recommended)
For most date comparison scenarios, I really like the between filter. It’s super handy for getting all records within a date range.

Here’s a quick example:

{
	filter: {
		start_time: {
			_between: [
				"2025-07-14T00:00:00Z",
				"2025-07-14T23:59:59Z"
			]
		}
	}
}

The alternative form to express this one is a bit more verbose.

{
	filter: {
		_and: [
			{
				start_time: {
					_gte: "2025-07-14T00:00:00Z"
				}
			},
			{
				end_time: {
					_lte: "2025-07-14T23:59:59Z"
				}
			}
		]
	}
}

This gives you everything that happened on July 14th, regardless of the specific time. Just have to make sure you’re matching up your input with what the field type is expecting.

The between filter is great for date-based queries - works great for daily, weekly, or monthly reports too! :sign_of_the_horns:

P.S. you might also play around with the $NOW dynamic variable.

{
  "datetime": {
    "_lte": "$NOW"
  }
}

It’s handy because you can do adjustments as well like $NOW(-1 year) or $NOW(+1 day)