...
| Code Block |
|---|
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 |
...
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:
| Code Block | ||
|---|---|---|
| ||
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:
| Code Block | ||
|---|---|---|
| ||
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:
| Code Block | ||
|---|---|---|
| ||
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:
| Code Block |
|---|
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. |