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.
Example:
Mach> CREATE TABLE after_table (id INTEGER);
Created successfully.
Mach> INSERT INTO after_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1);
1 row(s) inserted.
Mach> INSERT INTO after_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2);
Mach> INSERT INTO after_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3);
1 row(s) inserted.
Mach> INSERT INTO after_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4);
1 row(s) inserted.
Mach> INSERT INTO after_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5);
1 row(s) inserted.
Mach> select _arrival_time, * from after_table duration ALL after TO_DATE('2016-6-12 11:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 11:00:00 000:000:000 2
2016-06-12 12:00:00 000:000:000 3
2016-06-12 13:00:00 000:000:000 4
2016-06-12 14:00:00 000:000:000 5
[4] row(s) selected.
Mach> select _arrival_time, * from after_table duration ALL before TO_DATE('2016-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 13:00:00 000:000:000 4
2016-06-12 12:00:00 000:000:000 3
2016-06-12 11:00:00 000:000:000 2
2016-06-12 10:00:00 000:000:000 1
[4] row(s) selected.
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.
Example:
Mach> CREATE TABLE from_table (id INTEGER);
Created successfully.
Mach> INSERT INTO from_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1);
1 row(s) inserted.
Mach> INSERT INTO from_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2);
1 row(s) inserted.
Mach> INSERT INTO from_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3);
1 row(s) inserted.
Mach> INSERT INTO from_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4);
1 row(s) inserted.
Mach> INSERT INTO from_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 5);
1 row(s) inserted.
Mach> INSERT INTO from_table(_arrival_time, id) VALUES(TO_DATE('2016-6-12 15:00:00', 'YYYY-MM-DD HH24:MI:SS'), 6);
1 row(s) inserted.
Mach> SELECT _arrival_time, * FROM from_table DURATION FROM TO_DATE('2016-6-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS') TO TO_DATE('2016-6-12 14:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 12:00:00 000:000:000 3
2016-06-12 13:00:00 000:000:000 4
2016-06-12 14:00:00 000:000:000 5
[3] row(s) selected.
Mach> SELECT _arrival_time, * FROM from_table limit 2 DURATION FROM TO_DATE('2016-6-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS') TO TO_DATE('2016-6-12 15:00:00',
'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 12:00:00 000:000:000 3
2016-06-12 13:00:00 000:000:000 4
[2] row(s) selected.
Mach> SELECT _arrival_time, * FROM from_table DURATION FROM TO_DATE('2016-6-12 15:00:00', 'YYYY-MM-DD HH24:MI:SS') TO TO_DATE('2016-6-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 15:00:00 000:000:000 6
2016-06-12 14:00:00 000:000:000 5
2016-06-12 13:00:00 000:000:000 4
2016-06-12 12:00:00 000:000:000 3
[4] row(s) selected.
Mach> SELECT _arrival_time, * FROM from_table LIMIT 2 duration FROM TO_DATE('2016-6-12 15:00:00', 'YYYY-MM-DD HH24:MI:SS') TO TO_DATE('2016-6-12 12:00:00',
'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 15:00:00 000:000:000 6
2016-06-12 14:00:00 000:000:000 5
[2] row(s) selected.
Mach> SELECT _arrival_time, * from from_table duration FROM TO_DATE('2016-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS') TO TO_DATE('2016-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 13:00:00 000:000:000 4
[1] row(s) selected.
Mach> SELECT _arrival_time, * from from_table duration FROM TO_DATE('2016-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS') TO TO_DATE('2019-6-12 20:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 13:00:00 000:000:000 4
2016-06-12 14:00:00 000:000:000 5
2016-06-12 15:00:00 000:000:000 6
[3] row(s) selected.
Mach> SELECT _arrival_time, * from from_table duration FROM TO_DATE('2019-6-12 20:00:00', 'YYYY-MM-DD HH24:MI:SS') TO TO_DATE('2016-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2016-06-12 15:00:00 000:000:000 6
2016-06-12 14:00:00 000:000:000 5
2016-06-12 13:00:00 000:000:000 4
[3] row(s) selected.
SEARCH
Search Based on Absolute Time
Example:
Mach> CREATE TABLE time_table (id INTEGER);
Created successfully.
Mach> INSERT INTO time_table(_arrival_time, id) VALUES(TO_DATE('2014-6-12 10:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1);
1 row(s) inserted.
Mach> INSERT INTO time_table(_arrival_time, id) VALUES(TO_DATE('2014-6-12 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2);
1 row(s) inserted.
Mach> INSERT INTO time_table(_arrival_time, id) VALUES(TO_DATE('2014-6-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), 3);
1 row(s) inserted.
Mach> INSERT INTO time_table(_arrival_time, id) VALUES(TO_DATE('2014-6-12 13:00:00', 'YYYY-MM-DD HH24:MI:SS'), 4);
1 row(s) inserted.
Mach> INSERT INTO time_table VALUES(5);
1 row(s) inserted.
Mach> SELECT _arrival_time, * FROM time_table DURATION 1 MINUTE;
_arrival_time ID
-----------------------------------------------
2017-02-16 12:17:01 880:937:028 5
[1] row(s) selected.
Mach> SELECT _arrival_time, * FROM time_table DURATION 1 DAY BEFORE TO_DATE('2014-6-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS');
_arrival_time ID
-----------------------------------------------
2014-06-12 12:00:00 000:000:000 3
2014-06-12 11:00:00 000:000:000 2
2014-06-12 10:00:00 000:000:000 1
[3] row(s) selected.
Search Based on Relative Time
Example:
Mach> CREATE TABLE relative_table(id INTEGER); Created successfully. Mach> INSERT INTO relative_table values(1); 1 row(s) inserted. ------ WAIT for 30 SECONDS before the second value ------ Mach> INSERT INTO relative_table values(2); 1 row(s) inserted. Mach> SELECT _arrival_time, * FROM relative_table; _arrival_time ID ----------------------------------------------- 2017-02-16 12:35:34 476:055:014 2 2017-02-16 12:35:04 430:802:356 1 [2] row(s) selected. Mach> SELECT id FROM relative_table DURATION 30 second ; id -------------- 2 [1] row(s) selected. Mach> SELECT id FROM relative_table DURATION 60 second ; id -------------- 2 1 [2] row(s) selected. Mach> SELECT id FROM relative_table DURATION 30 second BEFORE 30 second; id -------------- 1 [1] row(s) selected.