Once you've connected your SQL database to Geckoboard, you can write your query and paste it into Geckoboard.

Before you write your query

There are a few things you need to know when writing your SQL query:

Columns must contain specific data for each visualization
We support loading data from views and tables
JSONB, Money and UNIX Epoch time are not supported in Geckoboard
Our query execution limit is 15 seconds
Our data limit is set to 4MB

Columns must contain specific data for each visualization

The following table lists the data your columns must contain for each visualization.

Visualization	Specification
Number	The result is a single numeric value.
Gauge	The result is a single numeric value.
Line Chart	The first column is a date/datetime (for X-axis labels) The remaining columns are all numeric
Column Chart	The first column is a date, datetime or string (for X-axis labels). The remaining columns are all numeric.
Bar Chart	There are exactly two columns. The first column is a date, datetime or string (for Y-axis labels). The second column is numeric.
Leaderboard	There are exactly two columns. The first column is a string (for Y-axis labels) The second column is numeric.
Table	There are ten columns or fewer.

Loading data from views and tables supported

The advantage of a view is that it can join data from several tables, creating a new view of it.

SELECT statements only

For security reasons, only SELECT statements are allowed. However, the following SELECT statements and functions are not yet supported:

UNION

Some data types are not supported

JSONB, Money and UNIX Epoch time are not supported in Geckoboard.

JSONB

To display JSONB data, you'll need to use CAST to convert it to another data type.

Example: using CAST to convert a JSONB datatype

To convert a JSONB data type, use the following:

SELECT surname, CAST(address as varchar) FROM gecko_keepers WHERE title = 'Dr

One value from the JSONB can be displayed as text in a table visualization by using this query:

SELECT surname, address ->> 'road' AS road FROM gecko_keepers WHERE title = 'Dr

Money

Money can be displayed in a table using CAST to convert from one data type into another.

Example: using CAST to convert currencies

To convert a currency to numeric, use the following:

SELECT destination_id, TRUNC(CAST(value_of_food_money AS NUMERIC)) AS Value, shipping_date from shipping_geckos

UNIX time

Only DATE and DATETIME types can be used to build historic visualizations.

Our query execution limit is 15 seconds

We have a query execution limit of 15 seconds. If you hit this limit, please take steps to simplify your query.

One solution for a longer execution time is to create another table where you save the processed data. Then you can fetch the data you want to show from that new table.

Our data limit is set to 4MB

We limit the size of data returned to 4MB. If you hit this limit, please take steps to simplify your query.

Running your SQL query

To run your SQL query in Databases, follow these steps:

Write, then copy, your SQL query. We recommend writing your query separately from Geckoboard. This means makes it easier to check your query syntax.

Notes on writing queries for Geckoboard:
- Queries don't need to be terminated with a semicolon (;).
- Avoid including comments. This includes single-line comments using the -- symbol and multi-line comments using the /* and */ symbols.
Switch to Geckoboard and paste your query into the code window. Then click Run query.
If successful, you'll see a confirmation. Depending on your query, you'll see one or more highlighted visualization options.
Hovering over each visualization type will tell you why it was, or wasn’t, suitable for your query. For a list of requirements for each visualization, see our table.
❗Need help with query errors? If you're experiencing errors, see if you need to use one of our workarounds. Otherwise, get in touch with our Customer Success team.
To add a widget, hover over your chosen visualization type and click the + button that replaces it.
With your Databases widget now added to your dashboard, you can set a refresh interval so it updates regularly. Refresh interval options range from 3 minutes to 12 hours, with a default set to 10 minutes.

Tips and workarounds

Avoid converting dates into strings

Some functions used for formatting dates are intended to convert the data type from "date" to "char". For example, using a query like this to get the month in which an event has happened:

SELECT TO_CHAR((Subscriptions.ended_at AT TIME ZONE 'UTC'), 'YYYY-MM') AS "month"

Instead, you can use a query like this as DATE_TRUNC preserves the datatype as "date":

DATE_TRUNC ('month',Subscriptions.ended_at AT TIME ZONE 'UTC') AS "month"

Minimum and maximum values

The following table illustrates the minimum and maximum values for integer and serial types:

Data type	Min value	Max value
Integer	-2147483648	2147483647
Serial	1	9007199254740991

You might experience a loss in precision when using real, double precision, numeric or decimal types with large or very small negative numbers.

We support Integer values up to 32 bits.

Bigint types not only consume a lot of storage but also decrease the performance of the database; therefore, you should have a good reason to use it.

Decimal points

When choosing how many decimal points you'd like, it's best to use the TRUNC function. TRUNC returns a truncated value of a number or a DateTime value. For more information, see PostgreSQL's list of Mathematical Functions and Operators.

Example uses of TRUNC

To round mean_temperature_c to 2 decimal points, use:

SELECT date_first_shipped_to, TRUNC(mean_temperature_c,2) as Temperature FROM destinations;

For no decimal points, use:

SELECT date_first_shipped_to, TRUNC(gravity) FROM destinations;

Create visualizations from SQL queries