Time Series Query in 1 Minute

In this posting, 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.b0fb4e1.official
Copyright 2014 InfiniFlux Corporation 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.