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?

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)