Time Zone support

Configure your time zone in Chartio to adjust your chart’s datetime values, as well as date variables ({TODAY}, {CURRENT_MONTH.START}, etc.) and filters.

Every dashboard has a time zone setting. By default, new dashboards will 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.

To set up time zone support in your Chartio account, complete the steps in the following sections: Set your organization default Time Zone, Enable or disable Time Zone Support for your data sources, and Customize Dashboard time zone.

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.

From the top navigation, select the ellipsis menu and choose Settings. 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.

Set the time zone in your account settings

Enable or disable Time Zone Support for your data sources

Choose whether individual data sources will have a time zone conversion applied to their data, and if so, which time zone conversion type (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, see below.

From the top navigation, choose Data Sources > your data source.

There are 3 data source time zone settings:

  • Disabled: use this setting if your data source does not need its time zone adjusted.
  • Time Zone: 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 savings time as needed.

Change time preferences in the data source settings

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 is not listed, it does not support either Time Zones or a UTC Offset. If you are unsure, contact support.

Database typeTime Zone or UTC Offset?Supported column data typesSQL Mode usage
PostgreSQL (includes Heroku, Data Stores, CSVs)Time Zonetimestamptz, timestamp1applied automatically1
MySQLTime Zone2timestampapplied automatically
Redshift (includes Stitch)Time Zonetimestamptz, timestamp1applied automatically1
Google Analyticsnone3
Google BigQueryUTC Offsettimestamprequires special syntax4
Google CloudSQLUTC Offsettimestamprequires special syntax5
SQL ServerUTC Offsetdatetimerequires special syntax6

TIME ZONE SQL MODE SYNTAX

If you are unsure what syntax is required for SQL Mode, simply add your date dimension in Interactive Mode, choose your desired time bucket (day, hour, etc.) and click Preview SQL to view an example.

  1. PostgreSQL, Redshift: In SQL Mode, cast timestamp and timestamptz columns to text in order to apply the time zone conversion, using TO_CHAR or ::text. 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 will need to 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 do not 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: For BigQuery, wrap timestamps in TIMESTAMP_ADD(timestamp_column, INTERVAL {UTC_OFFSET.RAW} HOUR)
  5. Google CloudSQL: For Google CloudSQL, wrap timestamps in CONVERT_TZ(date_column,'+00:00','{UTC_OFFSET.RAW}:00')
  6. SQL Server/SQL Azure: For SQL Server and SQL Azure, wrap datetimes in DATEADD(hour, {UTC_OFFSET.RAW}, date_column)

Customize Dashboard time zone

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

Set the time zone in the dashboard settings