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 UTC offset setting, which has to be manually changed twice a year to track daylight saving time.

By specifying a timezone, the database performs all the date math internally, which 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.

When a MySQL or PostgreSQL datasource has a timezone set, Chartio prefixes all queries to that datasource with a query 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

…you can see the timezone come into play with the executed query:

View executed query
This also happens with SQL Mode queries:

Also view executed query in SQL Mode

Redshift does not support changing the per-session timezone, so Chartio uses 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

Related Data Sources Help Articles

See more