# Create visualizations from SQL queries

Use SQL to fetch the data in your database and create visualizations.

July 17, 2025

## 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](https://support.geckoboard.com/en/articles/6055535-create-visualizations-from-sql-queries#h_b0dde34d2b)
- [We support loading data from views and tables](https://support.geckoboard.com/en/articles/6055535-create-visualizations-from-sql-queries#h_c3e97dd305)
- [`JSONB`, `Money` and `UNIX Epoch time` are not supported in Geckoboard](https://support.geckoboard.com/en/articles/6055535-create-visualizations-from-sql-queries#h_b395408edb)
- [Our query execution limit is 15 seconds](https://support.geckoboard.com/en/articles/6055535-create-visualizations-from-sql-queries#h_e275befce3)
- [Our data limit is set to 4MB](https://support.geckoboard.com/en/articles/6055535-create-visualizations-from-sql-queries#h_279d3c2558)

## 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)<br>  <br>- The remaining columns are all numeric |
| Column Chart | - The first column is a date, datetime or string (for X-axis labels).<br>  <br>- The remaining columns are all numeric. |
| Bar Chart | - There are exactly two columns.<br>  <br>- The first column is a date, datetime or string (for Y-axis labels).<br>  <br>- The second column is numeric. |
| Leaderboard | - There are exactly two columns.<br>  <br>- The first column is a string (for Y-axis labels)<br>  <br>- 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:

1. **Write**, then **copy**, your SQL query. We recommend writing your query separately from Geckoboard. This means makes it easier to [check your query syntax](https://www.eversql.com/sql-syntax-check-validator/).

**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.

2. Switch to Geckoboard and **paste** your query into the code window **.** Then click **Run query**.

3. If successful, you'll see a confirmation. Depending on your query, you'll see one or more highlighted visualization options.

4. To add a widget, hover over your chosen visualization type and click the **+** button that replaces it.

5. 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;
```