TimescaleDB

Timescale Connection

Premium

The premium version of Streamsheets comes with a TimescaleDB installation. It is a time series database (TSDB),which is a software system that is optimized for storing and serving time series through associated pairs of time(s) and value(s). It is based on PostgreSQL. To learn more, check their website.

Settings

By default leaving the connection parameter empty will automatically connect you to the integrated database. So in the case of using the integrated timescaledb, there is no need for a connection. If you want to connect to a timescaledb located somewhere else, create a connection. An account can be used to bundle the "settings" details in one place. Select an account to use these in your connection.

Host: The URL to a TimescaleDB instance.

Database: The database name to connect to.

Username, Password: To authenticate a client at a TimescaleDB, it is common to use a username and password to sign in. Different users can have different access rights to a database.

Connection Timeout Amount of seconds to wait for a response of the database, before interrupting the connection.

Logs

The Logs inform about the latest feedback of your endpoint. This can be successful or unsuccessful connection attempts. If you have trouble connecting, here is a good place to search for a reason.

Timescale Functions

Premium

To work with Timescale, Streamsheets uses a bunch of different functions.

• TIMESCALE.INSERT
• TIMESCALE.SELECT
• TIMESCALE.CREATE_TABLE
• TIMESCALE.SHOW_TABLES
• TIMESCALE.DROP_TABLE
• TIMESCALE.EXPORT.

The two most important functions are the inserting and selecting of data:

TIMESCALE.INSERT()

=TIMESCALE.INSERT(, TableName, ValuesJSON [, TableSchemaJSON])

You can use the insert function to fill a data table with the information.

Connection: Leave empty to connect to integrated TimescaleDB

TableName: Enter the table where it should save the data.

ValuesJSON: Enter a JSON with the data to be saved. E.g., JSON(A1:B4)

TableSchemaJSON: If you want to write data in a new table, you don´t necessarily need to use TIMESCALE.CREATE_TABLE. Adding your schema to the function will automatically create a new table while inserting data.

The schema describes the structure of your table. It refers to the organization of your table. Here you have to set the parameter names you want to save within your table and assign their data types. (E.g. "value1";"NUMERIC") Accepted data types are NUMERIC, TEXT, TIMESTAMP, TIMESTAMPTZ, DATE, INTEGER, etc.

TIMESCALE.SELECT()

=TIMESCALE.SELECT(Stream, SelectJSON [, Target, XValue,])

Select all from table "TimescaleDbTable", but limit response to 10.

To query data, use the select function. The select function uses the postgreSQL commands to query your data. Use a data range on your sheet to set your query command. These use the parameter value format. Use parameter like "select", " from", "where", "limit", "order by" etc. and define the command within your value cell.

Examples:

selecting "time" and "Sensor1" values for all "Sensor1" data over 40

selecting max "time" and renaming the parameter in the response using the "as" command.

selecting all saved parameter for a certain message id and limiting the amount of responses to 10. +

selecting the maximum value of "Sensor 1", in hourly time intervals, within a certain time frame, ordered descending by time interval, named "speed".

The select has to be a JSON, so use the JSON() function around the statement range.

Chart range:

You can base a chart range on TIMESCALE.SELECT() and display the response in, e.g., XY-Chart. To do so, add the X-axis parameter name for the chart in the TIMESCALE.SELECT() "XValue" parameter. E.g., If you want to display the time in the x-axis of your chart, choose this function:

=TIMESCALE.SELECT(,JSON(A1:B3),,"time")

Now the time parameter will be displayed on the x-axis of your chart. The chart only needs to reference the select function as the data source.

Timescale Tips & Tricks

TimescaleDB is based on the postgresql Syntax. Streamsheets simplify the creation of queries by using key and value pairs for a better overview. The key always resembles the command (select, from, where, etc.), while the value defines the details (What do you want to select? What is the table name? Any restrictions?).

Besides the regular SQL syntax, there are a couple of things worth mentioning. If you need a quick overview of possible syntax, click here.

• While saving a message Streamsheets automatically assigns the current timestamp. If you want to change this, add " time" to your schema and use a data type like timestamp or timestamptz. Your value has to be in a certain form. E.g. '1999-01-08 04:05:06'. Use single quotation marks around the value. For more information on the time syntax, click here.
• Commands must be written in lower case (select, from, etc.).
• Use double quotation marks around parameter names. E.g. MAX("Parameter").
• Use single quotation marks around dates, intervals and timestamps (SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), ' YEAR')).