How datasource time zones work

Data Sources

If you have a CSV, PostgreSQL, MySQL, or Redshift, there is a setting to configure the timezone. This replaces the Coordinated Universal Time (UTC) offset setting, which has to be manually changed twice a year to track the seasonal changes of daylight saving time. UTC does not observe daylight savings time.

When you specify a timezone, the database performs any of the required date math internally. This means that a query for hourly results for a month before daylight saving time took place will consistently return the same results regardless of whether daylight saving time is in place at the time of the query. This setting does not need to be changed per the restrictions of UTC offset we mentioned above.

When you are using MySQL or PostgreSQL and the datasource has a timezone set, Chartio prefixes all queries to that datasource with an additional line that changes the per-session timezone to the one configured for that datasource. You can see this if you set a timezone and then view the executed query in the chart editor. When the query is run using Interactive Mode…

Create a chart

…and then switch to SQL mode or preview the SQL, you can see the timezone come into play with the executed query:

View executed query
When using SQL mode Chartio will append this to the top of the query. For reference you can view this in the executed query:

Also view executed query in SQL Mode

Redshift does not support changing the per-session timezone, so Chartio will use a similar approach to the UTC offset setting and casts date columns to the configured timezone.

Timezone variable in the executed query

The timezone variable exists in all queries that use a datasource that has a timezone set. You can see this in use in a query:

Type the timezone variable in SQL Mode

These options give your dashboard the flexibility it needs to be a solution in any time zone.


Related Data Sources Help Articles

See more