home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
datatype

Usage

view: view_name {
  dimension: order_date {
    datatype: date
  }
}

Hierarchy

datatype

Possible Field Types

Dimension, Dimension Group, Filter, Measure

Default Value

timestamp
NOTE: See the section below about a possible exception for BigQuery connections.

Accepts

One of several possible values

Definition

The datatype parameter lets you specify the type of time or date data in your database table that you are supplying to your dimension, dimension_group, filter, or measure field. Matching the data type to your field can increase query performance.

For dimensions, filters, and measures the contain date or time data, and for dimension groups with type: time, the datatype parameter applies to the sql parameter of the dimension group.

For dimension groups with type: duration, the datatype parameter applies to both the sql_start and sql_end parameters, so be sure the sql_start and sql_end are both of the specified data type.

The datatype parameter accepts the following values:

The default value for datatype is timestamp.

See the section below about a possible exception for BigQuery connections.

Example

Create a dimension_group for a date field. Set the datatype: to date to increase query performance.

dimension_group: order_date type: time timeframes: [date, week, month, year] datatype: date sql: ${TABLE}.order_date

Things to Consider

Handling of datatype for BigQuery Connections

Most SQL dialects treat datetime values as timestamps. Because of that, Looker automatically casts datetime values as timestamps when generating SQL commands. BigQuery, however, does distinguish between datetimes and timestamps, so Looker is transitioning to a new behavior starting in Looker 7.14:

In order to support existing BigQuery projects, Looker 7.14 introduces the Treat the Datatype Datetime as a Timestamp legacy feature, which applies only to BigQuery connections:

The legacy feature allows Looker developers on BigQuery projects to maintain the previous behavior for BigQuery projects that were created before 7.14.

The Treat the Datatype Datetime as a Timestamp legacy feature will be removed in a future release, so Looker developers with BigQuery projects should adopt the new behavior and turn off the legacy features as soon as possible. See the Legacy Features Deprecation Schedule documentation page for details.

The Treat the Datatype Datetime as a Timestamp legacy feature applies to the entire Looker instance. When the legacy feature is enabled on the Looker instance, Looker developers can turn off the legacy behavior on a model-by-model basis by adding this in their model file:

bigquery_datetime_as_timestamp: no

When a model is set to bigquery_datetime_as_timestamp: no, any fields with datatype: datetime on a BigQuery connection will be treated as datetimes. This is the updated behavior for BigQuery connections and allows Looker developers to verify that each project is correctly configured before turning off the Treat the Datatype Datetime as a Timestamp legacy feature on the Looker instance.

Before turning off the legacy feature, be sure to:

Top