DURATION...BEFORE
DURATION...AFTER
DURATION...FROM/TO
SEARCH
The DURATION clause is used in the SELECT statement to set the data retrieval scope as a time reference. The biggest advantage of this DURATION is that it greatly affects SELECT performance because the database narrows the range of data to visit. As mentioned above, Machbase is extremely easy to access data based on specific time because all data is partitioned based on time axis. One important point is that the reference time for this duration is not the user-specified column, but the ‘_arrival_time’ column, which is an internal data type. Therefore, to use Machbase most efficiently, you do not need to define a separate time column, but rather use the internal column ‘_arrival_time’, which is specified at the moment when data is stored in Machbase.
Machbase outputs data in the reverse order. That is from the latest data to old data and all the basic data operations follow this order. However, because it is also common to actually retrieve data from a certain point in the past to future directions, Machbase supports AFTER command. The syntax is defined as follows.
Syntax:
DURATION time_expression [BEFORE time_expression | TO_DATE(time) ]; DURATION time_expression [AFTER TO_DATE(time)]; time_expression - ALL - n year - n month - n week - n day - n hour - n minute - n second
DURATION...BEFORE
As described earlier, if BEFORE is explicitly or omitted, it searches from records in the "reverse order"(i.e., from the current time to the past time direction).
DURATION...AFTER
When AFTER command is explicitly used, it searches data from the past to present. In other words, records will be displayed in forward direction of time. Check the scan direction in the figure below. In the previous section, BEFORE command searches and displays records from the recent to past. While AFTER command searches, it displays data from the past to recent. It is because the time is based on the past when using AFTER and it sounds natural to display data from the old one first.
DURATION...FROM/TO
If a user wants to search data based on two specified absolute times, use "DURATION FROM A TO B" syntax. A and B represent the absolute time values that are represented as TO_DATE(). A and B can have different values based on the users' intention. For example, if A is bigger than B, the scan will be conducted in forward direction, that is, from the recent to past. It is the same direction with BEFORE command. However, if B is bigger than A, the scan will be conducted in reverse direction. That is, it searches from the past to recent and is the same direction with AFTER command.
The absolute times and direction of scans are shown below.