GroveStreams Help Center
Welcome to GS SQL!



Overview

The complexity of querying time-series data with standard SQL can be overwhelming and come with performance bottlenecks. That's why we designed our own structured query language, based on standard SQL, but with extensions, to give you the tools to accomplish your tasks easily and quickly.


Conventions
 		Grammar
Tables
Security
Examples
Data Types
 		Date and Time
Data Type Conversions
In-Line Views or Subqueries
Sorting
APIs


Conventions

The initial release of GroveStreams SQL is based on SQLite with much of the SQLite SELECT functionality implemented.
  • Most items are case in-sensitive including table names, column names, functions and other reserved words.
  • Comments are supported within SQL statements:
    1. To comment out a single line use --
    2. To comment out multiple lines use /* */
  • String concatenation can be done with the + operator. Some databases use || for concatenation. || in GS SQL represents the OR predicate clause.
  • Use "AND" or && in your WHERE conditions. Use "OR" or "||" in your WHERE conditions.
  • Datetimes are represented by epoch millisecond Longs. See Date and Time below
  • Use Sample column parameters to make time-series requests simpler and faster:
    1. SELECT Sample(Range(sd=-1d, ed=now))
    2. SELECT Sample(Range(sd=-1M, ed=streamEd))
    3. SELECT Sample(Range(last=12))
    4. SELECT Sample(cycleId='day', stat='max', timeFilterId='weekends', Range(currentCycle='year'))
    5. See the Grammar page for more Sample details and examples.
  • NULL
    1. NULL represents a missing or unknown value.
    2. A String can be Blank or NULL: (string1 == "") is not the same as (string1 IS NULL)
    3. Interval streams can have interval samples with a value of NULL. These are considered gap intervals.
    4. It is not possible to test for NULL values with comparison operators, such as =, ==, <, or <>. Use the IS NULL and IS NOT NULL operators instead.
    5. Rows will be filtered out whenever one side of an expression is a NULL value. Use IS NULL or IS NOT NULL to avoid this scenario.

Security

Query results are filtered based on role-based security configured on content folders. For example, only the component names that the user has "Read" capabilities will be returned for queries such as this: "SELECT DISTINCT cname FROM Stream"

Only Session and OAuth tokens can be used for API calls that execute queries since query result filtering is done by using the calling user's content rights. api_key tokens are not allowed for these types of calls.

Defined Query object results can be retrieved using api_key tokens via the GET query/{queryUid}/results API call. Defined Query objects can be found under the Tools tab within Observation Studio.

Data Types

The number of data types supported have been limited to keep things simple. Supported Data types:
  • DOUBLE: A double-precision 64-bit IEEE 754 floating point. It generally represents decimal numbers and is based on the Java Double data type.
  • LONG: A non-decimal signed number with a range of -9,223,372,036,854,775,808 ... 9,223,372,036,854,775,807 and is based on the Java Long data type.
  • STRING: A set of zero or more characters and is based on the Java String data type.
  • BOOLEAN: Used to store only two possible values, either true or false and is based on the Java Boolean data type.
  • BIGDECIMAL: Arbitrary-precision signed decimal numbers and is based on the Java BigDecimal data type.

Date and Time

Using epoch millis as a datetime representation has become common practice in most modern applications as it greatly simplifies datetime management. GS SQL uses the same practice. That is, it doesn't have a DATE or TIME data type. Date and time are stored and managed as single Long data types which are epoch milliseconds. GroveStreams SQL provides functions to convert Long epoch millis to readable dates for your locale. Datetimes can be stored as Strings, but GroveStreams will not "know" it is a date and manage it as a String.

Search the web for more information on "epoch milliseconds" to learn more.

Data Type Conversions

  • Sample Float types will be converted to Doubles
  • Sample Short and Integer types will be converted to Longs
  • If more than one Sample value data type is being returned in a single column, the following logic is applied in this order:
    1. If one is a String, they all are converted to Strings
    2. If one is a Big Decimal, they all are converted to BigDecimal
    3. If one is a Double, they all are converted to Double
    4. If one is a Long, they all are converted to Long
    5. All Boolean
  • The above logic applies to LAST_VALUE columns too
  • If an expression has a String on one side and a number on the other, an attempt will be made to convert the String to a number.

In-Line Views or Subqueries

In-line views, sometimes known as subqueries, are supported. They must be surrounded with parentheses and have an alias.
SELECT cname, lastValue FROM Stream INNER JOIN
(SELECT cuid FROM Stream WHERE id='squareFeet' && sample > 10000) AS table2 ON cuid == table2.cuid
WHERE id='region' && sample IN ('west', 'east')

Correlated subqueries are supported and are evaluated once for each row processed by the parent statement.
SELECT DISTINCT cname, id, (SELECT MAX(sample) FROM stream ss WHERE s1.id == 'kWh') as xx FROM Stream s1 WHERE id == 'kWh' ORDER BY cname


Sorting

Sample times will automatically be sorted for most queries. Use ORDER BY when they are not.

Examples

Support for Expressions with or without columns
SELECT ((3*2)+3/10


Current time
SELECT formatDate(now)


Return a sorted list of all components
SELECT DISTINCT cname AS 'Component' FROM Stream ORDER BY Component


Return a list of all components and their streams
SELECT cname AS 'Component', name AS 'Stream' FROM Stream ORDER BY Component, Stream


Return a list of Components that belong to a specific folder
SELECT DISTINCT cname AS 'Component' FROM Stream WHERE folderPath == '/Components'


Return a list of Components that belong to a specific folder and all of its subfolders
SELECT DISTINCT cname AS 'Component' FROM Stream WHERE startsWith(folderPath, '/Components')


Return the last sample and time for a specific stream
SELECT lastValue, formatDate(endDate) AS 'time' FROM Stream WHERE cid='generator22' && id='kw'


Return the last sample and time for a collection of streams with IDs that start with 'energy'
SELECT lastValue, formatDate(endDate) AS 'time' FROM Stream WHERE id LIKE 'energy*'


Return the last 100 samples and times for a specific stream
SELECT formatDate(time) AS 'time', sample( range(last=100) ) FROM Stream WHERE cid='generator22' && id='kw'


Return the last 100 samples and times for a specific stream with values greater than 100
SELECT formatDate(time) AS 'time', sample( range(last=100) ) FROM Stream WHERE cid='generator22' && id='kw' && sample > 100


Return a list of components that have a point stream with ID='region' and a value of either 'west' or 'east'
SELECT DISTINCT cname AS 'Station Name', lastValue FROM Stream WHERE id='region' && sample IN ('west', 'east')


Return a list of components with two point stream conditions (in-line view must be in parentheses and have an Alias)
SELECT DISTINCT cname AS 'Station Name', lastValue FROM Stream INNER JOIN
(SELECT cuid FROM Stream WHERE id='squareFeet' && sample > 10000) AS table2 ON cuid == table2.cuid
WHERE id='region' && sample IN ('west', 'east')


Return a list of components with three point stream conditions
SELECT DISTINCT cname AS 'Station Name', lastValue FROM Stream INNER JOIN
(SELECT cuid FROM Stream WHERE id='squareFeet' && sample > 10000) AS table2 ON cuid == table2.cuid INNER JOIN
(SELECT cuid FROM Stream WHERE id='managed' && sample == true) AS table3 ON table2.cuid == table3.cuid
WHERE id='region' && sample IN ('west', 'east')


Return a list of components with three regular stream last sample value conditions
SELECT DISTINCT cname AS 'Station Name', lastValue FROM Stream INNER JOIN
(SELECT cuid FROM Stream WHERE id='squareFeet' && lastValue > 10000) AS table2 ON cuid == table2.cuid INNER JOIN
(SELECT cuid FROM Stream WHERE id='managed' && lastValue == true) AS table3 ON table2.cuid == table3.cuid
WHERE id='region' && lastValue IN ('west', 'east')


Return hourly maximum afternoon temperatures for the last 24 hours (or 1 day)
SELECT cname AS 'Weather Station', name AS 'Temperature', time, sample(cycleId='hour', stat='max', range(sd=-1d, ed=now), TimeFilterId='afternoon' ) AS 'Afternoon Hourly Highs' FROM Stream WHERE id LIKE 'temp*'


Query for this month's samples which are located in east or west regions (as indicated by point streams)
SELECT cname, name, timeSd, timeEd, sample( Range( currentCycle='month') ) FROM Stream AS stream INNER JOIN
(SELECT sample, cuid FROM Stream WHERE id = 'region' && sample IN ('east', 'west')) AS region on stream.cuid = region.cuid
WHERE stream.id='kWh' Order By cname


Return the Spring and Summer samples for the previous year; ordered by time
SELECT formatDate(time) AS 'time', sample( timeFilterId='spring', range(previousCycle='year') ) FROM Stream WHERE cid='generator22'
UNION ALL
SELECT formatDate(time) AS 'time', sample( timeFilterId='summer', range(previousCycle='year') ) FROM Stream WHERE cid='generator22'
ORDER BY time


Return the number of components created by month
SELECT COUNT(DISTINCT cuid) AS 'New Components', FORMATDATE(ccreatedDate, 'yyyy/MM') AS Month from Stream WHERE ccreatedDate IS NOT NULL
GROUP BY FORMATDATE(ccreatedDate, 'yyyy/MM') ORDER BY 2


Return the number of components created today
SELECT COUNT(DISTINCT cuid) AS 'New Components Today' FROM Stream WHERE YEAR(ccreatedDate) = YEAR(NOW) && MONTH(ccreatedDate) = MONTH(NOW) && DAY(ccreatedDate) = DAY(NOW)


Return the number of streams created by week of year
SELECT FORMATDATE(ccreatedDate, 'yyyy/MM') AS Date, FORMATDATE(ccreatedDate, 'yyyy/ww') AS 'Week of Year', COUNT(uid) AS 'New Streams' from Stream WHERE ccreatedDate IS NOT NULL && YEAR(ccreatedDate) = 2023 GROUP BY FORMATDATE(ccreatedDate, 'yyyy/w') ORDER BY 2