Time Series Query in 1 Minute

In this tutorial, you will learn how to retrieve data with DURATION syntax. Machbase provides DURATION syntax to support time series query.

Step 1 : Get MIN and MAX time in sample_table​

[mach@localhost ~]$ machsql -s localhost -u sys -p manager
=================================================================
     Machbase Client Query Utility
     Release Version 3.5.0.826b8f2.official
     Copyright 2014 Machbase Inc. or its subsidiaries.
     All Rights Reserved.
=================================================================
MACHBASE_CONNECT_MODE=INET, PORT=5656
Type 'help' to display a list of available commands.

Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME) FROM SAMPLE_TABLE;
MIN(_ARRIVAL_TIME)              MAX(_ARRIVAL_TIME)
-------------------------------------------------------------------
2015-05-20 06:00:00             2015-05-20 06:40:10
[1] row(s) selected.

Step 2 : Retrieve statistics data with DATE_TRUNC() function which grouping by time.

Mach> SELECT DATE_TRUNC('minute', _ARRIVAL_TIME) as TIME, COUNT(*) as COUNT
FROM SAMPLE_TABLE
GROUP BY TIME
ORDER BY TIME;

TIME                            COUNT
--------------------------------------------------------
2015-05-20 06:00:00             32001
2015-05-20 06:01:00             28000
2015-05-20 06:02:00             24000
2015-05-20 06:03:00             32000
2015-05-20 06:04:00             16000
2015-05-20 06:05:00             16000
...
2015-05-20 06:38:00             24000
2015-05-20 06:39:00             16000
2015-05-20 06:40:00             3999
[41] row(s) selected.
Elapsed time: 0.304
Mach>

Step 3 : Retrieve data with various DURATION cases.

Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME), COUNT(*)  FROM SAMPLE_TABLE DURATION 1 MINUTE BEFORE TO_DATE('2015-05-20 06:30:00');
MIN(_ARRIVAL_TIME)              MAX(_ARRIVAL_TIME)              COUNT(*)
-----------------------------------------------------------------------------------------
2015-05-20 06:29:05             2015-05-20 06:29:45             20000
[1] row(s) selected.


Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME), COUNT(*)  FROM SAMPLE_TABLE DURATION 1 MINUTE AFTER TO_DATE('2015-05-20 06:30:00');
MIN(_ARRIVAL_TIME)              MAX(_ARRIVAL_TIME)              COUNT(*)
-----------------------------------------------------------------------------------------
2015-05-20 06:30:04             2015-05-20 06:30:57             28000
[1] row(s) selected.

Mach> SELECT MIN(_ARRIVAL_TIME), MAX(_ARRIVAL_TIME), COUNT(*)  FROM SAMPLE_TABLE DURATION FROM TO_DATE('2015-05-20 06:20:00') TO TO_DATE('2015-05-20 06:30:00');
MIN(_ARRIVAL_TIME)              MAX(_ARRIVAL_TIME)              COUNT
-----------------------------------------------------------------------------------------
2015-05-20 06:20:03             2015-05-20 06:29:45             252000
[1] row(s) selected.

results matching ""

    No results matching ""