Both startDate and endDate seem to be unoptimized columns from the SQL query. That is why they are using the UDFs (pr_read_from_stream function) in the query.
In the BLOB, we always store the date or datetime value in GMT. But SYSDATE will always be in the timezone of the database. In your case since the database is in UTC and the value in BLOB is also UTC, the difference in date should be working correctly.
So I am still not able to understand why EST (or EDT) as the operator time zone is causing issues for you? Is this because converting it to GMT is changing the date and we are not considering the time part?
Yes, the problem here it will not take the operator timezone. Let us say the operator is in EST zone, After 8 PM EST the current date time returned will be one day ahead as it is in GMT, when this is compared with the end date which is a date value with no time , alerts applicable alerts for that day will not appear
So this is expected behaviour and will happen the other way around if someone uses a time zone ahead of UTC. Since the data in the database does not have the time part, this will happen. Can we have the data stored in DB along with time so that you get accurate results?