Data source time zone support

Time zone support can be turned on or off per data source. If, for example, your data source is already in your desired time zone, you may disable time zone support for that data source.

Configure your time zone in Atlassian Analytics to adjust your chart’s date and time values, as well as relative date variables (for example, {TODAY}, {CURRENT_MONTH.START}, etc.) and filters.

Every dashboard has a time zone setting. By default, new dashboards use the default time zone of your workspace, but you can customize the time zone for individual dashboards if needed.

Options for time zone support

For each data source, you can choose whether or not to apply a time zone conversion to its data and what type of time zone conversion to apply. Learn how to change time zone support in data source settings.

There are three options for time zone support:

Disabled

Does not apply any time zone conversion to the data. Use this setting if your data source already uses the desired time zone.

Time zone

Adds the SET TIME ZONE 'timezone_name' clause to your queries.

Note that the “Time zone” option is only available for data sources that support named time zones.

UTC offset

Adjusts the datetime values in your data source by adding or subtracting hours from UTC time. The UTC offset is automatically populated based on the dashboard’s time zone setting and also accounts for Daylight Saving Time as needed.

Time zone versus UTC offset

We recommend using the “Time zone” option to the “UTC offset” option, especially if you’ll mostly use SQL mode queries.

The following data sources have the time zone support option:

• Amazon Redshift

• MySQL

• PostgreSQL

Time zone syntax

For visual mode queries

If “Time zone” is selected for a data source, Atlassian Analytics will automatically apply the appropriate syntax to convert the values in any columns with a data type of timestamptz or timestamp. However, MySQL only supports converting values in columns with a data type of timestamp.

For SQL mode queries

To ensure the “Time zone” option works for your SQL mode queries, there are extra formatting steps you’ll need to make to your datetime columns.

If you’re unsure what syntax is required for SQL mode queries:

1. Add your date column to a visual mode query.

2. Select your desired time bucket in the column’s aggregation menu (for example, Day, Hour, etc.).

3. Expand the advanced drawer to preview the auto-generated SQL to view the syntax that’s applied to the column.

For PostgreSQL and Redshift queries:

1. Use TO_CHAR or ::text to cast timestamp and timestamptz columns to text.

2. In the SELECT statement, use the following syntax on your timestamp column: datetime_column AT TIME ZONE 'UTC'.

For example, if you had a timestamp column called Created at, you’d use the following SELECT statement in your SQL query:

For MySQL queries:

Run the following query to check if your MySQL database has the necessary time zone tables loaded into it: SELECT * FROM mysql.time_zone_name;

If there are no results, refer to the MySQL documentation for instructions on how to populate the time zone tables.

If you don’t want want to load the time zone tables into your database, use UTC offset for your data source instead.

For Google BigQuery queries:

Google BigQuery only supports UTC offset for columns with a data type of timestamp.

Use the following syntax on your timestamp columns: TIMESTAMP_ADD(timestamp_column, INTERVAL {UTC_OFFSET.RAW} HOUR)

For SQL Server queries:

SQL Server only supports UTC offset for columns with a data type of datetime.

Use the following syntax on your datetime columns: DATEADD(hour, {UTC_OFFSET.RAW}, datetime_column)