How can I add timezone to Esper queries?

Question

I am using Esper & I need to filter events by their timestamp. The events come from an external source.

The challenge is that the cutoff instant is at a different timezone than the events` timestamp, e.g. the cutoff instant is at 3:30 CET (e.g. Prague time) while the timestamp field of the event is at UTC.

This poses a problem when the timezone shifts to Daylight Savings Time, because the cutoff instant needs to be modified in the query. E.g. in this case, if the cutoff instant is 3:30 CET, during winter time it would be on 2:30 UTC and during DST it would be on 1:30 UTC. It means that I have to change the query when the time shifts into and out of DST.

This is the current query:

SELECT * 
FROM   my_table
WHERE  timestamp_field.after( timestamp.withtime(2,30,0,0) ) 

I would like to have a robust solution that will save me the hassle of changing the cutoff timestamp queries every few months. Can I add the timezone to the query statement itself? Is there any other solution?

Answer 1

After struggling unsuccessfully with trying ot do it in the WHERE caluse or using a Pattern, I managed to solve the issue using a [Single-Row Function plugin][1].

I pass the plugin function the cutoff hour, timezone & event timezone and compute the cutoff hour in the event's timezone.

My query changed to:

SELECT * 
FROM   my_table
WHERE  timestamp_field.after( timestamp.withtime(
       eventTZHour(2, 'UTC', 'Europe/Prague'), 30, 0, 0) ) 

I added the Java implementation in a class:

public class EsperPlugins {
    public int eventTZHour(int hour, String eventTZ, String cutoffTZ) {
       // return tz calculations
    }
}

and finally registered the plugin in esper.cfg.xml:

<esper-configuration>
    <plugin-singlerow-function name="eventTZHour"
         function-class="EsperPlugins"
         function-method="eventTZHour"/>
</esper-configuration>

[1]: http://www.espertech.com/esper/release-5.2.0/esper-reference/html/extension.html#custom-singlerow-function from esper's docs