www.espertech.comDocumentation
EPL date-time methods work on date-time values to perform common tasks such as comparing times and time periods, adding or subtracting time periods, setting or rounding calendar fields and querying fields.
Date-time methods operate on:
Any expression or event property that returns one of the below values:
A long-type millisecond or microsecond value.
A java.util.Calendar
object including subclasses.
A java.util.Date
object including subclasses.
A java.time.LocalDateTime
object including subclasses.
A java.time.ZonedDateTime
object including subclasses.
Any event for which the event type declares a start timestamp property name and optionally also an end timestamp property name. Date-time methods operate on events by means of the stream-alias.method-name syntax.
Your application may add additional date-time methods by following the recipe described at Section 22.8, “Date-Time Method”.
The below table summarizes the built-in date-time methods available:
The syntax for date-time methods is the same syntax as for any chained invocation:
input_val.datetime_method_name( [method_parameter [, method_parameter [,...]]]) .[ datetime_method_name(...) [...]]
select current_timestamp.withTime(1, 0, 0, 0) as time1am from MyEvent
As date-time methods can be chained, this EPL is equivalent:
select current_timestamp.set('hour', 1).set('min', 0).set('sec', 0).set('msec', 0) as time1am from MyEvent
You may apply a date-time method to an event property.
select timeTaken.roundFloor('min') as timeTakenRounded from RFIDEvent
select rfid.after(wifi) as isAfter from RFIDEvent#lastevent rfid, WifiEvent#lastevent wifi
input_val.between(range_start, range_end [, include_start, include_end])
select timeTaken.between(timeStart, timeEnd) from RFIDEvent
select timeTaken.between(timeStart, timeEnd, false, false) from RFIDEvent
The format
date-time method formats the date-time returning a string.
The method takes either no parameter or a single format parameter.
The get
date-time method returns the value of the given date-time value field.
The method takes a single string-constant field name as parameter. Please see Section 5.2.1, “Specifying Time Periods” for a list of recognized keywords (not case-sensitive).
The method returns the numeric value of the field within the date-time value. The value returned adheres to Calendar
-class semantics: For example, the value for month
starts at zero and has a maximum of 11 (Note: for LocalDateTime
and ZonedDateTime
the range for month is 1 to 12).
The example below outputs the month value of the time-taken property value of the RFID event:
select timeTaken.get('month') as timeTakenMonth from RFIDEvent
The example below outputs the month value of the time-taken property value of the RFID event:
select timeTaken.getMonthOfYear() as timeTakenMonth from RFIDEvent
The minus
date-time method returns a date-time with the specified duration taken away.
The method has two versions: The first version takes the duration as a long-type millisecond value. The second version takes the duration as a time-period expression, see Section 5.2.1, “Specifying Time Periods”.
The example below demonstrates the time-period parameter to subtract two minutes from the time-taken property value of the RFID event:
select timeTaken.minus(2 minutes) as timeTakenMinus2Min from RFIDEvent
The next example is equivalent but passes a millisecond-value instead:
select timeTaken.minus(2*60*1000) as timeTakenMinus2Min from RFIDEvent
The plus
date-time method returns a date-time with the specified duration added.
The method has two versions: The first version takes the duration as a long-type millisecond value. The second version takes the duration as a time-period expression, see Section 5.2.1, “Specifying Time Periods”.
The next example adds two minutes to the time-taken property value of the RFID event:
select timeTaken.plus(2 minutes) as timeTakenPlus2Min from RFIDEvent
The next example is equivalent but passes a millisecond-value instead:
select timeTaken.plus(2*60*1000) as timeTakenPlus2Min from RFIDEvent
The roundCeiling
date-time method rounds to the highest whole unit of the date-time field.
The method takes a single string-constant field name as parameter. Please see Section 5.2.1, “Specifying Time Periods” for a list of recognized keywords (not case-sensitive).
The next example rounds-to-ceiling the minutes of the time-taken property value of the RFID event:
select timeTaken.roundCeiling('min') as timeTakenRounded from RFIDEvent
If the input time is 2002-05-30 09:01:23.050
, for example, the output is 2002-05-30 09:02:00.000
(example timestamps are in format yyyy-MM-dd HH:mm:ss.SSS
).
The roundFloor
date-time method rounds to the lowest whole unit of the date-time field.
The method takes a single string-constant field name as parameter. Please see Section 5.2.1, “Specifying Time Periods” for a list of recognized keywords (not case-sensitive).
The next example rounds-to-floor the minutes of the time-taken property value of the RFID event:
select timeTaken.roundFloor('min') as timeTakenRounded from RFIDEvent
If the input time is 2002-05-30 09:01:23.050
, for example, the output is 2002-05-30 09:01:00.000
(example timestamps are in format yyyy-MM-dd HH:mm:ss.SSS
).
The roundFloor
date-time method rounds to the nearest whole unit of the date-time field.
The method takes a single string-constant field name as parameter. Please see Section 5.2.1, “Specifying Time Periods” for a list of recognized keywords (not case-sensitive).
The next example rounds the minutes of the time-taken property value of the RFID event:
select timeTaken.roundHalf('min') as timeTakenRounded from RFIDEvent
The following table provides a few examples of the rounding (example timestamps are in format yyyy-MM-dd HH:mm:ss.SSS
):
This method is not support for LocalDateTime
and ZonedDateTime
input values.
The method takes a string-constant field name and an expression returning an integer-value as parameters. Please see Section 5.2.1, “Specifying Time Periods” for a list of recognized keywords (not case-sensitive).
The method returns the new date-time value with the field set to the provided value. Note that value adheres to Calendar
-class semantics: For example, the value for month
starts at zero and has a maximum of 11 (Note: for LocalDateTime
and ZonedDateTime
the range for month is 1 to 12).
The example below outputs the time-taken with the value for month set to April:
select timeTaken.set('month', 3) as timeTakenMonth from RFIDEvent
The method takes three expressions as parameters: An expression for year, month and day.
The example below outputs the time-taken with the date set to May 30, 2002:
select timeTaken.withDate(2002, 4, 30) as timeTakenDated from RFIDEvent
The method takes a string-constant field name as parameter. Please see Section 5.2.1, “Specifying Time Periods” for a list of recognized keywords (not case-sensitive).
The method returns the new date-time value with the specific date field set to the maximum value.
The example below outputs the time-taken property value with the second-part as 59 seconds:
select timeTaken.withMax('sec') as timeTakenMaxSec from RFIDEvent
The method takes a string-constant field name as parameter. Please see Section 5.2.1, “Specifying Time Periods” for a list of recognized keywords (not case-sensitive).
The method returns the new date-time value with the specific date field set to the minimum value.
The example below outputs the time-taken property value with the second-part as 0 seconds:
select timeTaken.withMin('sec') as timeTakenMaxSec from RFIDEvent
The example below outputs the time-taken with the time set to 9am:
select timeTaken.withTime(9, 0, 0, 0) as timeTakenDated from RFIDEvent
The toCalendar
date-time method returns the Calendar
object for this date-time value.
The method takes no parameters.
The example below outputs the time-taken as a Calendar
object:
select timeTaken.toCalendar() as timeTakenCal from RFIDEvent
Interval algebra methods compare start and end timestamps of events or timestamps in general.
create schema A as (startts long, endts long) starttimestamp 'startts' endtimestamp 'endts'
create schema B as (startts long, endts long) starttimestamp 'startts' endtimestamp 'endts'
select * from A#lastevent as a, B#lastevent as b where a.before(b)
The first parameter of each interval algebra methods is the event or timestamp to compare to.
All remaining parameters to interval algebra methods are intervals and can be any of the following:
A time period expression as described in Section 12.4.11, “Includes”. For example: a.before(b, 1 hour 2 minutes)
.
When an interval parameter is provided and is null, the method result value is null.
select * from A#lastevent as a, B#lastevent as b where a.after(b) // Above matches when: // a.startTimestamp - b.endTimestamp > 0
select * from A#lastevent as a, B#lastevent as b where a.after(b, 5 sec) // Above matches when: // a.startTimestamp - b.endTimestamp >= 5 seconds
select * from A#lastevent as a, B#lastevent as b where a.after(b, 5 sec, 10 sec) // Above matches when: // 5 seconds <= a.startTimestamp - b.endTimestamp <= 10 seconds
select * from A#lastevent as a, B#lastevent as b where a.before(b) // Above matches when: // b.startTimestamp - a.endTimestamp > 0
select * from A#lastevent as a, B#lastevent as b where a.before(b, 5 sec) // Above matches when: // b.startTimestamp - a.endTimestamp >= 5 seconds
select * from A#lastevent as a, B#lastevent as b where a.before(b, 5 sec, 10 sec) // Above matches when: // 5 seconds <= b.startTimestamp - a.endTimestamp <= 10 seconds
select * from A#lastevent as a, B#lastevent as b where a.coincides(b) // Above matches when: // a.startTimestamp = b.startTimestamp and a.endTimestamp = b.endTimestamp
select * from A#lastevent as a, B#lastevent as b where a.coincides(b, 5 sec) // Above matches when: // abs(a.startTimestamp - b.startTimestamp) <= 5 sec and // abs(a.endTimestamp - b.endTimestamp) <= 5 sec
select * from A#lastevent as a, B#lastevent as b where a.coincides(b, 5 sec, 10 sec) // Above matches when: // abs(a.startTimestamp - b.startTimestamp) <= 5 seconds and // abs(a.endTimestamp - b.endTimestamp) <= 10 seconds
The method determines whether the input value's start and end timestamp are during the first parameter's start and end timestamp. The symmetrical opposite is Section 12.4.11, “Includes”.
If used with one parameter, for example in a.during(b)
, the method returns true if the start timestamp of A is after the start timestamp of B and the end timestamp of A is before the end timestamp of B.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.during(b) // Above matches when: // b.startTimestamp < a.startTimestamp <= a.endTimestamp < b.endTimestamp
If providing two parameters, for example in a.during(b, 5 sec)
, the method returns true if the difference between the start timestamps of A and B is equal to or less then 5 seconds and the difference between the end timestamps of A and B is also equal to or less then 5 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.during(b, 5 sec) // Above matches when: // 0 < a.startTimestamp - b.startTimestamp <= 5 sec and // 0 < a.endTimestamp - b.endTimestamp <= 5 sec
If providing three parameters, for example in a.during(b, 5 sec, 10 sec)
, the method returns true if the difference between the start timestamps of A and B and the difference between the end timestamps of A and B is between 5 and 10 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.during(b, 5 sec, 10 sec) // Above matches when: // 5 seconds <= a.startTimestamp - b.startTimestamp <= 10 seconds and // 5 seconds <= a.endTimestamp - b.endTimestamp <= 10 seconds
If providing five parameters, for example in a.during(b, 5 sec, 10 sec, 20 sec, 30 sec)
, the method returns true if the difference between the start timestamps of A and B is between 5 seconds and 10 seconds and the difference between the end timestamps of A and B is between 20 seconds and 30 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.during(b, 5 sec, 10 sec, 20 sec, 30 sec) // Above matches when: // 5 seconds <= a.startTimestamp - b.startTimestamp <= 10 seconds and // 20 seconds < a.endTimestamp - b.endTimestamp <= 30 seconds
The method determines whether the input value's start timestamp is after the first parameter's start timestamp and the end timestamp of the input value and the first parameter are the same. The symmetrical opposite is Section 12.4.10, “Finished By”.
If used with one parameter, for example in a.finishes(b)
, the method returns true if the start timestamp of A is after the start timestamp of B and the end timestamp of A and B are the same.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.finishes(b) // Above matches when: // b.startTimestamp < a.startTimestamp and a.endTimestamp = b.endTimestamp
If providing two parameters, for example in a.finishes(b, 5 sec)
, the method returns true if the start timestamp of A is after the start timestamp of B and the difference between the end timestamps of A and B is equal to or less then 5 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.finishes(b, 5 sec) // Above matches when: // b.startTimestamp < a.startTimestamp and // abs(a.endTimestamp - b.endTimestamp ) <= 5 seconds
A negative value for interval parameters is not allowed. If your interval parameter is itself an expression that returns a negative value the runtime logs a warning message and returns null.
The method determines whether the input value's start timestamp happens before the first parameter's start timestamp and the end timestamp of the input value and the first parameter are the same. The symmetrical opposite is Section 12.4.9, “Finishes”.
If used with one parameter, for example in a.finishedBy(b)
, the method returns true if the start timestamp of A is before the start timestamp of B and the end timestamp of A and B are the same.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.finishedBy(b) // Above matches when: // a.startTimestamp < b.startTimestamp and a.endTimestamp = b.endTimestamp
If providing two parameters, for example in a.finishedBy(b, 5 sec)
, the method returns true if the start timestamp of A is before the start timestamp of B and the difference between the end timestamps of A and B is equal to or less then 5 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.finishedBy(b, 5 sec) // Above matches when: // a.startTimestamp < b.startTimestamp and // abs(a.endTimestamp - b.endTimestamp ) <= 5 seconds
The method determines whether the first parameter's start and end timestamp are during the input value's start and end timestamp. The symmetrical opposite is Section 12.4.8, “During”.
If used with one parameter, for example in a.includes(b)
, the method returns true if the start timestamp of B is after the start timestamp of A and the end timestamp of B is before the end timestamp of A.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.includes(b) // Above matches when: // a.startTimestamp < b.startTimestamp <= b.endTimestamp < a.endTimestamp
If providing two parameters, for example in a.includes(b, 5 sec)
, the method returns true if the difference between the start timestamps of A and B is equal to or less then 5 seconds and the difference between the end timestamps of A and B is also equal to or less then 5 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.includes(b, 5 sec) // Above matches when: // 0 < b.startTimestamp - a.startTimestamp <= 5 sec and // 0 < a.endTimestamp - b.endTimestamp <= 5 sec
If providing three parameters, for example in a.includes(b, 5 sec, 10 sec)
, the method returns true if the difference between the start timestamps of A and B and the difference between the end timestamps of A and B is between 5 and 10 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.includes(b, 5 sec, 10 sec) // Above matches when: // 5 seconds <= a.startTimestamp - b.startTimestamp <= 10 seconds and // 5 seconds <= a.endTimestamp - b.endTimestamp <= 10 seconds
If providing five parameters, for example in a.includes(b, 5 sec, 10 sec, 20 sec, 30 sec)
, the method returns true if the difference between the start timestamps of A and B is between 5 seconds and 10 seconds and the difference between the end timestamps of A and B is between 20 seconds and 30 seconds.
Sample EPL:
select * from A#lastevent as a, B#lastevent as b where a.includes(b, 5 sec, 10 sec, 20 sec, 30 sec) // Above matches when: // 5 seconds <= a.startTimestamp - b.startTimestamp <= 10 seconds and // 20 seconds <= a.endTimestamp - b.endTimestamp <= 30 seconds
select * from A#lastevent as a, B#lastevent as b where a.meets(b) // Above matches when: // a.endTimestamp = b.startTimestamp
select * from A#lastevent as a, B#lastevent as b where a.meets(b, 5 sec) // Above matches when: // abs(b.startTimestamp - a.endTimestamp) <= 5 seconds
select * from A#lastevent as a, B#lastevent as b where a.metBy(b) // Above matches when: // a.startTimestamp = b.endTimestamp
select * from A#lastevent as a, B#lastevent as b where a.metBy(b, 5 sec) // Above matches when: // abs(a.startTimestamp - b.endTimestamp) <= 5 seconds
select * from A#lastevent as a, B#lastevent as b where a.overlaps(b) // Above matches when: // a.startTimestamp < b.startTimestamp < a.endTimestamp < b.endTimestamp
select * from A#lastevent as a, B#lastevent as b where a.overlaps(b, 5 sec) // Above matches when: // a.startTimestamp < b.startTimestamp < a.endTimestamp < b.endTimestamp and // 0 <= a.endTimestamp - b.startTimestamp <= 5 seconds
select * from A#lastevent as a, B#lastevent as b where a.overlaps(b, 5 sec, 10 sec) // Above matches when: // a.startTimestamp < b.startTimestamp < a.endTimestamp < b.endTimestamp and // 5 seconds <= a.endTimestamp - b.startTimestamp <= 10 seconds
select * from A#lastevent as a, B#lastevent as b where a.overlappedBy(b) // Above matches when: // b.startTimestamp < a.startTimestamp < b.endTimestamp < a.endTimestamp
select * from A#lastevent as a, B#lastevent as b where a.overlappedBy(b, 5 sec) // Above matches when: // b.startTimestamp < a.startTimestamp < b.endTimestamp < a.endTimestamp and // 0 <= b.endTimestamp - a.startTimestamp <= 5 seconds
select * from A#lastevent as a, B#lastevent as b where a.overlappedBy(b, 5 sec, 10 sec) // Above matches when: // b.startTimestamp < a.startTimestamp < b.endTimestamp < a.endTimestamp and // 5 seconds <= b.endTimestamp - a.startTimestamp <= 10 seconds
select * from A#lastevent as a, B#lastevent as b where a.starts(b) // Above matches when: // a.startTimestamp = b.startTimestamp and a.endTimestamp < b.endTimestamp
select * from A#lastevent as a, B#lastevent as b where a.starts(b, 5 sec) // Above matches when: // abs(a.startTimestamp - b.startTimestamp) <= 5 seconds and // a.endTimestamp < b.endTimestamp
select * from A#lastevent as a, B#lastevent as b where a.startedBy(b) // Above matches when: // a.startTimestamp = b.startTimestamp and b.endTimestamp < a.endTimestamp
select * from A#lastevent as a, B#lastevent as b where a.startedBy(b, 5 sec) // Above matches when: // abs(a.startTimestamp - b.startTimestamp) <= 5 seconds and // b.endTimestamp < a.endTimestamp