Appearance
Dates, times, and timezones
This page describes which timezone is used to display and aggregate dates and times in SQL results. The display timezone depends on the date and time type and on where the value is displayed.
Kinds of dates and times
Date and time types in data warehouses fall into three kinds, depending on whether they have a time and a timezone.
| Kind | Description |
|---|---|
| Datetime with a timezone | Represents a specific moment. The displayed time changes with the timezone. |
| Datetime without a timezone | Represents a date and time as shown on a clock. The displayed time is the same in any timezone. |
| Date | Represents a date without a time. |
Type names differ between data warehouses. For example, TIMESTAMP is a datetime with a timezone in BigQuery, and a datetime without a timezone in Redshift.
| Data warehouse | Datetime with a timezone | Datetime without a timezone |
|---|---|---|
| BigQuery | TIMESTAMP | DATETIME |
| Snowflake | TIMESTAMP_TZ, TIMESTAMP_LTZ | TIMESTAMP_NTZ |
| Redshift | TIMESTAMPTZ | TIMESTAMP |
| Databricks | TIMESTAMP | TIMESTAMP_NTZ |
Display timezone
Datetimes with a timezone are displayed as follows, depending on where they're displayed. Datetimes without a timezone and dates are never shifted, wherever they're displayed.
| Where it's displayed | Display |
|---|---|
| SQL block results | Shows the database value as is. This is the same on any page. |
| Places in charts that treat datetimes as time (such as time axes, table cells, and big number values) | Uses the viewer's timezone. |
| Places in charts that treat datetimes as categories (such as pie chart item names, legends, and category axes) | Shows the database value as is. |
Result values inserted as is into text or tables in markup pages, or formatted with formatDate() | Uses the viewer's timezone. |
| Values processed with other expressions in markup pages | Shows the database value as is. |
To change how values are displayed, you can do the following.
- To display SQL block results in the viewer's timezone in a readable format, use a table chart. You can also set the format with the date formatter without changing the SQL.
- To set the format or timezone where the database value is shown as is, convert the value to a string in SQL (for example, BigQuery's
FORMAT_TIMESTAMP('%Y-%m-%d', ts, 'Asia/Tokyo')). - To show the same time to every viewer regardless of their timezone, convert the value to a datetime without a timezone in SQL (for example, BigQuery's
DATETIME(ts, 'Asia/Tokyo')).
Truncating and extracting in charts
When a chart dimension truncates or extracts a datetime with a timezone, the calculation uses the dimension's Timezone. For example, if Timezone is UTC, truncating by day splits days at midnight UTC.
Places that treat datetimes as time display them as follows. Places that treat them as categories show the database value as is, even for truncated values.
| Target | Display |
|---|---|
Truncating by YEAR / MONTH / WEEK / DAY | Uses the dimension's Timezone. Every viewer sees the same date, regardless of their timezone. |
Truncating by HOUR / MINUTE | Uses the viewer's timezone. |
| Extracting | The number extracted in the dimension's Timezone is shown as is. |
Exported files
Exported PNG and PDF files show dates and times in the timezone specified for the export.
| Export method | Timezone |
|---|---|
| Screenshot in workflows | The Timezone of the Run Report specified in Run ID |
| Exports by the AI agent | The timezone specified for the report run (UTC if not specified) |
notebook render page / notebook render chart in Codatum CLI | The timezone specified with --tz (the local timezone of the environment if not specified) |
Date parameters
The Date input and Date range parameters are passed to SQL as dates without a time or timezone. The date that a relative date refers to depends on the timezone of the place that calculates it (such as the browser).