Time Zone Support

Configure your time zone in Chartio to adjust your chart’s datetime values, as well as Relative Date Variables (e.g., {TODAY}, {CURRENT_MONTH.START}, etc.) and filters.

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

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.

Set your organization default time zone

The organization default time zone will be used to set the default time zone for every new dashboard, Explore mode, and report dates.

Go to your organization’s Settings page. Under the Organization tab, you’ll see a dropdown labeled Time Zone. From this dropdown, select a time zone option and click Update to save and apply the changes.

Note: If you change the organization default time zone, any dashboards currently using the default time zone will update to use the new default.

Enable or disable Time Zone Support for your data sources

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 (i.e., named time zones or UTC offset).

Named time zones are preferable, especially if you plan to use SQL Mode, because the syntax is simpler. However, not all databases support named time zones. If you aren’t sure if your database supports the SET TIME ZONE syntax, refer to the Time Zone SQL Mode syntax section below.

To change your data source time zone settings, go to the Data Sources page, select your data source, and update the Time Zone Support setting under the General settings tab.

There are three Time Zone Support options:

• Disabled: use this setting if your data source does not need its time zone adjusted.
• Time Zone: use this if your data source supports named time zones; the data source will use the SET TIME ZONE 'timezone_name' syntax for its queries. If you don’t see Time Zone as an option, your database type does not support named time zones.

• UTC Offset: the data source’s datetime values will be adjusted to your time zone by adding or subtracting hours from UTC. The UTC Offset is automatically populated based on the dashboard’s Time Zone setting, and will account for Daylight Saving Time as needed.

  

Time Zone vs. UTC Offset

Time Zone is preferable to UTC Offset, especially if you’ll be using SQL mode. Check your database type below to see how time zones are supported. If your database type isn’t listed, it doesn’t support either Time Zones or a UTC Offset. If you’re unsure, contact Chartio Support.

Time Zone SQL Mode syntax

If you’re unsure what syntax is required for SQL Mode, simply add your date dimension in Visual Mode, choose your desired time bucket (e.g., Day, Hour, etc.) then click Preview SQL to view an example.

1. PostgreSQL, Redshift: Use TO_CHAR or ::text to cast timestamp and timestamptz columns to text in order to apply the time zone conversion. In addition, timestamp columns must be set to UTC before the time zone conversion can be applied, using syntax SELECT (date_column AT TIME ZONE 'UTC').
2. MySQL: In order to use the Time Zone setting with MySQL, you must have the time zone tables in your database. MySQL includes the tables automatically, but they may need to be manually loaded into the database. To check whether the time zone tables are already loaded, run the following query: select * from mysql.time_zone_name; If there are no results, see MySQL’s documentation for instructions on Populating the Time Zone tables. If you don’t want to complete this process, you can use a UTC Offset instead.
3. Google Analytics: Set your dashboard’s time zone to match your Google Analytics time zone.
4. BigQuery: Wrap timestamps in TIMESTAMP_ADD(timestamp_column, INTERVAL {UTC_OFFSET.RAW} HOUR)
5. Google CloudSQL: Wrap timestamps in CONVERT_TZ(date_column,'+00:00','{UTC_OFFSET.RAW}:00')
6. SQL Server/SQL Azure: Wrap datetimes in DATEADD(hour, {UTC_OFFSET.RAW}, date_column)

Customize dashboard time zone

By default, new dashboards use the organization’s default time zone. However, you may override the organization default time zone on a per-dashboard basis in the dashboard’s settings.