Note that you are viewing Looker documentation. For Looker Studio documentation, visit https://support.google.com/looker-studio.

Dimension, filter, and parameter types

This page refers to the type parameter that is part of a dimension or filter.

type can also be used as part of a measure, described on the Measure types documentation page.

type can also be used as part of a dimension group, described on the dimension_group parameter documentation page.

Usage

view: view_name {
  dimension: field_name {
    type: field_type
  }
}

Hierarchy

View File

view

Field Type

type

Possible Field Types

Dimension, Filter, Parameter

Default Value

string

Accepts

A dimension, filter, or parameter type

This page includes additional details about the various types that can be assigned to a dimension, filter, or parameter. A dimension, filter, or parameter can only have one type, which defaults to string if no type is specified.

Some types have supporting parameters, which are described within the appropriate section.

Type definitions

D = Dimension

DG = Dimension group

F = Filter

P = Parameter

Type	Description	Valid Field Types
`bin`	ADDED 21.14 For fields that group numerical values into several ranges	D
`date`	For fields that contain dates	D F P
`date_time`	For fields that contain dates and times	D F P
`distance`	For fields that calculate the distance of the most direct path ("as the crow flies") between two `type: location` dimensions	D
`duration`	Used with a `dimension_group` to create several duration-based dimensions from a single table column. For information about dimension groups, see the `dimension_group` parameter documentation page.	DG
`location`	For fields that are based on a latitude and longitude and will be used in visualizations	D
`number`	For fields that contain numbers	D F P
`string`	For fields that contain letters or special characters	D F P
`tier`	For fields that group numerical values into several ranges	D
`time`	Used with a `dimension_group` to create several time-based dimensions from a single table column. For information about dimension groups, see the `dimension_group` parameter documentation page.	DG
`unquoted`	For `parameter` fields whose values will be inserted directly into SQL, and therefore shouldn't be quoted (as they would with `type: string`)	P
`yesno`	For fields that show if something is true or false	D F P
`zipcode`	For fields that contain a zip code and will be used in visualizations	D
Individual Time and Date Types	A rarely used alternative to `type: time` for creating single, time-based dimensions	D F
Individual Duration Types	A rarely used alternative to `type: duration` for creating single time-based dimensions that calculate time differences	D
`int`	REMOVED 5.4 Replaced by `type: number`	D

`bin`

type: bin is an alias for type: tier. The two types can be used interchangeably.

type: bin is used in conjunction with the bins parameter to separate a numeric dimension into a set of number ranges. For example, you might bin an age dimension into different age ranges. You can change how the bins appear in the Looker UI with the style parameter.

The usage pattern is:

view: view_name {
  dimension: field_name {
    type: bin
    bins: [numeric_value, numeric_value, ... ]
    style: interval
    sql: ${my_field_name} ;;
  }
}

The sql parameter for type: bin dimensions can take any valid SQL expression that results in a number or an integer.

An example using age might look like this:

dimension: age_bin {
  type: bin
  bins: [0, 10, 20, 30, 40, 50, 60, 70, 80, 90]
  style: interval
  sql: ${age} ;;
}

The way that this would appear in the Looker UI is described in the style section for the type: tier parameter on this page.

The bin type is an alias for type: tier. The two types can be used interchangeably, and the behavior is the same for both:

The style subparameter is used to customize the appearance of bins in the Looker UI.
Dimensions of type: bin cannot be used in custom filters.
Using type: bin in conjunction with dimension fill can result in unexpected tier buckets.

`distance`

type: distance is used to calculate the distance of the most direct path ("as the crow flies") between two type: location dimensions.

The sql parameter for type: distance dimensions is excluded. Instead, you supply a reference to a type: location dimension in the start_location_field and end_location_field parameters.

The usage is:

view: view_name {
  dimension: field_name {
    type: distance
    start_location_field: field_name_1
    end_location_field: field_name_2
    units: kilometers
  }
}

The unit of distance is determined by the units parameter, which can take the following values:

feet
kilometers
meters
miles
nautical_miles
yards

For example, you might calculate the distance traveled by a customer to pickup a rental like this:

dimension: distance_to_pickup {
  type: distance
  start_location_field: customer.home_location
  end_location_field: rental.pickup_location
  units: miles
}

The distance calculated will be the most direct path between the two points, not necessarily the distance traveled by road.

Don't use the ${view_name.field_name} syntax in the start_location_field and end_location_field parameters. Instead, use the view name and field name by themselves, like view_name.field_name.

`duration`

type: duration is used in conjunction with a dimension_group to create a set of calculated time differences between dimensions and/or SQL expressions.

type: duration works only with a dimension_group and will not work with a regular dimension. However, you can specify individual duration-based dimensions, as explained in Individual duration types.

For information about dimension groups with type: duration, see the dimension_group parameter documentation page.

`location`

type: location is used in conjunction with the sql_latitude and sql_longitude parameters to create coordinates that you want to plot on a Map or Static Map (Points) visualization (use a state or country field for Static Map (Regions)), or that you want to use in a type: distance calculation.

The usage pattern is:

view: view_name {
  dimension: field_name {
    type: location
    sql_latitude:${field_name_1} ;;
    sql_longitude:${field_name_2} ;;
  }
}

The sql parameter for type: location dimensions is excluded. Instead, you supply any valid SQL expression that results in a decimal latitude or longitude to the sql_latitude and sql_longitude parameters. These are usually references to LookML fields that contain latitude or longitude information, but they can be static values if you wanted to have a location of your headquarters, or something along those lines.

For example, you might create a store_location dimension like this:

dimension: store_location {
  type: location
  sql_latitude: ${store_latitude} ;;
  sql_longitude: ${store_longitude} ;;
}

If you don't want to plot the locations or calculate distances, you can use a simpler field type such as type: number. When you view a location in a table, Looker will show the value from your database, as well as automatically generate a link to that location in Google Maps.

Supported database dialects for `location`

For Looker to support type: location in your Looker project, your database dialect must also support it. The following table shows which dialects support type: location in the latest release of Looker:

Dialect	Supported?
Actian Avalanche	Yes
Amazon Athena	Yes
Amazon Aurora MySQL	Yes
Amazon Redshift	Yes
Apache Druid	Yes
Apache Druid 0.13+	No
Apache Druid 0.18+	Yes
Apache Hive 2.3+	Yes
Apache Hive 3.1.2+	Yes
Apache Spark 3+	Yes
ClickHouse	Yes
Cloudera Impala 3.1+	Yes
Cloudera Impala 3.1+ with Native Driver	Yes
Cloudera Impala with Native Driver	Yes
DataVirtuality	Yes
Databricks	Yes
Denodo 7	Yes
Denodo 8	Yes
Dremio	Yes
Dremio 11+	Yes
Exasol	Yes
Firebolt	Yes
Google BigQuery Legacy SQL	Yes
Google BigQuery Standard SQL	Yes
Google Cloud PostgreSQL	Yes
Google Cloud SQL	Yes
Google Spanner	Yes
Greenplum	Yes
HyperSQL	Yes
IBM Netezza	Yes
MariaDB	Yes
Microsoft Azure PostgreSQL	Yes
Microsoft Azure SQL Database	Yes
Microsoft Azure Synapse Analytics	Yes
Microsoft SQL Server 2008+	Yes
Microsoft SQL Server 2012+	Yes
Microsoft SQL Server 2016	Yes
Microsoft SQL Server 2017+	Yes
MongoBI	Yes
MySQL	Yes
MySQL 8.0.12+	Yes
Oracle	Yes
Oracle ADWC	Yes
PostgreSQL 9.5+	Yes
PostgreSQL pre-9.5	Yes
PrestoDB	Yes
PrestoSQL	Yes
SAP HANA 2+	Yes
SingleStore	Yes
SingleStore 7+	Yes
Snowflake	Yes
Teradata	Yes
Trino	Yes
Vector	Yes
Vertica	Yes

`number`

type: number is used with numbers or integers.

The sql parameter for type: number dimensions can take any valid SQL expression that results in a number or an integer.

type: number fields can be formatted by using the value_format or value_format_name parameters.

For example, the following LookML creates a field called profit based on the revenue and cost fields, then displays it in a money format ($1,234.56):

dimension: profit {
  type: number
  sql: ${revenue} - ${cost} ;;
  value_format_name: usd
}

A dimension can only perform arithmetic on other dimensions, not measures. Additionally, type: number dimensions won't provide suggestions to users, even if you are using them to display ID numbers.

`string`

type: string is typically used with fields that contain letters or special characters. It can be used with number fields as well, although Looker has better features for handling numbers if you use type: number instead.

The sql parameter for type: string dimensions can take any valid SQL expression.

For example, the following LookML creates the field full_name by combining a field called first_name and last_name:

dimension: full_name {
  type: string
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}

In this example type: string could be omitted, because string is the default value for type.

`tier`

You can use type: bin as an alias for type: tier. The two types can be used interchangeably.

type: tier is used in conjunction with the tiers parameter to separate a numeric dimension into a set of number ranges. For example, you might tier an age dimension into different age ranges. You can change how the tiers appear in the Looker UI with the style parameter.

The usage pattern is:

view: view_name {
  dimension: field_name {
    type: tier
    tiers: [numeric_value, numeric_value, ... ]
    style: interval
    sql: ${my_field_name} ;;
  }
}

The sql parameter for type: tier dimensions can take any valid SQL expression that results in a number or an integer.

An age example might look like this:

dimension: age_tier {
  type: tier
  tiers: [0, 10, 20, 30, 40, 50, 60, 70, 80]
  style: classic # the default value, could be excluded
  sql: ${age} ;;
}

The way that this would appear in the Looker UI is described in the style section on this page.

Dimensions of type: tier cannot be used in custom filters.

`style`

The style parameter lets you change the way that tiers appear in the Looker UI. Although not shown in the examples in the following sections, if there were negative numbers in the data, there would be a beginning tier that would include all numbers from negative infinity up to but not including 0. There are four possible values:

classic (the default value)
interval
integer
relational

`classic`

style: classic is the default, and looks like:

Users Age Tier (Classic)	Users Count
T02 [10,20)	949
T03 [20,30)	2,161
T04 [30,40)	2,114
T05 [40,50)	2,081
T06 [50,60)	2,024
T07 [60,70)	1,511
T08 [70,inf)	1,450

You can interpret this tier notation as follows:

T02 [10,20) is the range including 10, and up to but not including 20
T09 [80,inf) is the range including 80, and up to infinity

`interval`

style: interval is similar to style: classic, but it doesn't have the leading TXX labels. It looks like:

Users Age Tier (Interval)	Users Count
[10,20)	949
[20,30)	2,161
[30,40)	2,114
[40,50)	2,081
[50,60)	2,024
[60,70)	1,511
[70,inf)	1,450

`integer`

style: integer must be used with discrete integer values (such as age). If you try to use non-integers to define the tiers you will receive an error. This style looks like:

Users Age Tier (Integer)	Users Count
10 to 19	949
20 to 29	2,161
30 to 39	2,114
40 to 49	2,081
50 to 59	2,024
60 to 69	1,511
70 or Above	1,450

`relational`

style: relational is best used with continuous numbers (such as dollars) and looks like:

Users Age Tier (Relational)	Users Count
>=10 and < 20	949
>=20 and < 30	2,161
>=30 and < 40	2,114
>=40 and < 50	2,081
>=50 and < 60	2,024
>=60 and < 70	1,511
>=70	1,450

You can also style tiers with value_format. For example:

dimension: amount_tier {
  type: tier
  tiers: [0, 10, 20, 30, 40, 50, 60, 70, 80]
  style: integer
  sql: ${amount} ;;
  value_format: "$#,##0"
}

This example would result in tier labels like $10 to $19, $20 to $29, and the like.

Things to consider

Using tier in conjunction with dimension fill can result in unexpected tier buckets.

For example, a dimension of type: tier, Age Tier, will display tier buckets for Below 0 and 0 to 9 when dimension fill is enabled, although the data doesn't include age values for those buckets:

Users Age Tier
Below 0
0 to 9
10 to 19
20 to 29
30 to 39
40 to 49
50 to 59
60 to 69
70 to 79
80 or Above

When dimension fill is disabled for Age Tier, the buckets more accurately reflect the age values that are available in the data:

Users Age Tier
10 to 19
20 to 29
30 to 39
40 to 49
50 to 59
60 to 69
70 to 79
80 or Above

You can enable or disable dimension fill by hovering over the dimension name in the Explore, clicking the field-level gear icon, and selecting either Remove Filled in Tier Values to disable, or Fill in Missing Tier Values to enable.

`time`

type: time is used in conjunction with a dimension_group and the timeframes parameter to create a set of time-based dimensions. For example, you can create a date, week, and month dimension based on a single timestamp column.

type: time works only with a dimension_group and will not work with a regular dimension. However, you can specify individual time-based dimensions, as explained in the Individual time and date types section.

For information about dimension groups, see the dimension_group parameter documentation page, which also includes information on the timeframes, convert_tz, and datatype parameters, as well as common challenges and caveats to consider when you are using time-based data.

`unquoted`

type: unquoted is used only with parameter fields. The unquoted type is similar to type: string, except that when the value of the parameter is inserted into the {% parameter %} liquid variable it won't be quoted. This is useful when inserting values into SQL, such as column or table names, that cannot be quoted in order to work properly.

Inserting unquoted values directly into SQL could create the possibility of unwanted SQL actions. To address this, parameter values of type: unquoted are restricted to the characters A through Z and 0 through 9 (no spaces or other special characters).

As an example, the following LookML creates a parameter called table_name that will produce an unquoted value:

parameter: table_name {
  type: unquoted
}

`yesno`

type: yesno creates a field that indicates if something is true or false. The values appear as Yes and No in the Explore UI.

The sql parameter for a type: yesno dimension takes a valid SQL expression that evaluates to TRUE or FALSE. If the condition evaluates to TRUE, Yes is displayed to the user; otherwise, No is displayed.

The SQL expression for type: yesno dimensions cannot include any aggregations. This means it cannot contain SQL aggregations or any references to LookML measures. If you want to create a yesno field that includes a SQL aggregation or that references a LookML measure, use a measure with type: yesno, not a dimension.

For example, the following LookML creates a field that indicates whether or not an order has been paid, based on the status field:

dimension: is_order_paid {
  type: yesno
  sql: ${status} = 'paid' ;;
}

To reference a type: yesno field in another field, treat the type: yesno field as a boolean (in other words, as if it contains a true or false value already). For example:

dimension: is_big_order {
  type: yesno
  sql: ${order_size} = 'big' ;;
}
# This is correct
measure: total_boxes_needed {
  type: number
  sql: SUM(CASE WHEN ${is_big_order} THEN 2 ELSE 1 END) ;;
}
# This is NOT correct
measure: total_boxes_needed {
  type: number
  sql: SUM(CASE WHEN ${is_big_order} = 'Yes' THEN 2 ELSE 1 END) ;;
}

If you use type: yesno with time-based data, the dimension returns yes if the datetime has a value, and returns no if not.

`zipcode`

type: zipcode is used with zip code dimensions that you want to plot on a Static Map (Points) visualization (use a state or country field for Static Map (Regions)). Any dimension of type: zipcode is automatically given the map_layer_name of us_zipcode_tabulation_areas. If you don't want to plot the zip codes, you can use a simpler type such as type: number.

The sql parameter for type: zipcode dimensions can take any valid SQL expression that results in a five-digit, US zipcode.

For the purposes of filtering on a zipcode dimension, some database dialects require that the database field referenced by the zipcode dimension is a varchar or string type field, not an integer type field.

For example:

dimension: zip {
  type: zipcode
  sql: ${TABLE}.zipcode ;;
}

Individual time and date types

Typically, dates are handled as a dimension_group that uses type: time.

It is possible to create one dimension or filter field for each individual timeframe that you want to include, instead of generating all of them in a single dimension_group. This is generally avoided, unless you have already pre-calculated time columns in your database or want to change the Looker timeframe naming convention (such as having a field named created_date_of_purchase instead of created_date).

Many individual time and date based types are listed in Available time-based types.

As an example, for this dimension_group definition:

dimension_group: created {
  type: time
  timeframes: [week, month, year]
  sql: ${TABLE}.created_at ;;
}

You can use this as a logic equivalent:

dimension: created_week {
  type: date_week
  sql: ${TABLE}.created_at ;;
}
dimension: created_month {
  type: date_month
  sql: ${TABLE}.created_at ;;
}
dimension: created_year {
  type: date_year
  sql: ${TABLE}.created_at ;;
}

Available time-based types

The following types are used in the type parameter of an individual dimension to create time or date based fields. Do not use these types with the timeframe parameter, which is documented on the dimension_group documentation page.

All individual time and date types require a timestamp as input from your database.

Special types

Type	Description	Example Output
`date_raw`	The raw value from your database, without casting or time zone conversion, won't show up on an Explore page (typically not needed except in joins or time comparisons)	`2014-09-03 17:15:00 +0000`

Time types

Type	Description	Example Output
`date_time`	Datetime of the underlying field (some SQL dialects show as much precision as your database contains, while others show only to seconds)	`2014-09-03 17:15:00`
`date_time_of_day`	Time of day	`17:15`
`date_hour`	Datetime truncated to the nearest hour	`2014-09-03 17`
`date_hour_of_day`	Integer hour of day of the underlying field	`17`
`date_hourX`	Splits each day into intervals with the specified number of hours.	See Using `date_hourX`.
`date_minute`	Datetime truncated to the nearest minute	`2014-09-03 17:15`
`date_minuteX`	Splits each hour into intervals with the specified number of minutes.	See Using `date_minuteX`.
`date_second`	Datetime truncated to the nearest second	`2014-09-03 17:15:00`
`date_millisecond`	Datetime truncated to the nearest millisecond (see the Dialect Support for Milliseconds and Microseconds section for information on dialect support)	`2014-09-03 17:15:00.000`
`date_millisecondX`	Splits each second into intervals with the specified number of milliseconds (see the Dialect Support for Milliseconds and Microseconds section for information on dialect support).	`2014-09-01 01:00:00.250`
`date_microsecond`	Datetime truncated to the nearest microsecond (see the Dialect Support for Milliseconds and Microseconds section for information on dialect support)	`2014-09-03 17:15:00.000000`

Date types

Type	Description	Example Output
`date`	Date of the underlying field	`2017-09-03`
`date_date`	REMOVED 4.6 Replaced by `date`

Week types

Type	Description	Example Output
`date_week`	Date of the week starting on a Monday of the underlying datetime	`2017-09-01`
`date_day_of_week`	Day of week alone	`Wednesday`
`date_day_of_week_index`	Day of week index (0 = Monday, 6 = Sunday)	`2`

Note that the date_week, date_day_of_week, and date_day_of_week_index types depend on the value of week_start_day, which defaults to Monday.

Month types

Type	Description	Example Output
`date_month`	Year and month of the underlying datetime	`2017-09`
`date_month_num`	Integer number of the month of the underlying datetime	`9`
`date_month_name`	Name of the month	`September`
`date_day_of_month`	Day of month	`3`
`date_fiscal_month_num`	Integer number of the month of the underlying datetime	`9`

To use date_fiscal_month_num type, the fiscal_month_offset parameter must be set in the model.

Quarter types

Type	Description	Example Output
`date_quarter`	Year and quarter of the underlying datetime	`2017-Q3`
`date_quarter_of_year`	Quarter of the year preceded by a "Q"	`Q3`
`date_fiscal_quarter`	Fiscal year and quarter of the underlying datetime	`2017-Q3`
`date_fiscal_quarter_of_year`	Fiscal quarter of the year preceded by a "Q"	`Q3`

To use date_fiscal_quarter and date_fiscal_quarter_of_year types, the fiscal_month_offset parameter must be set in the model.

Year types

Type	Description	Example Output
`date_year`	Integer year of the underlying date time	`2017`
`date_day_of_year`	Day of year	`143`
`date_week_of_year`	Week of the year as a number	`17`
`date_fiscal_year`	Integer fiscal year of the underlying datetime	`2017`

To use the date_fiscal_year type, the fiscal_month_offset parameter must be set in the model.

Using `date_hourX`

In date_hourX the X is replaced with 2, 3, 4, 6, 8, or 12.

This will split up each day into intervals with the specified number of hours. For example date_hour6 will split each day into 6 hour segments, which will appear like:

2014-09-01 00:00:00
2014-09-01 06:00:00
2014-09-01 12:00:00
2014-09-01 18:00:00

For example, a row with a time of 2014-09-01 08:03:17 would have a date_hour6 of 2014-09-01 06:00:00.

Using `date_minuteX`

In date_minuteX the X is replaced with 2, 3, 5, 10, 15, or 30.

Using date_minuteX will split up each hour into intervals with the specified number of minutes. For example, date_minute15 will split each hour into 15-minute segments, which will appear as follow:

2014-09-01 01:00:00
2014-09-01 01:15:00
2014-09-01 01:30:00
2014-09-01 01:45:00

To give an example, a row with a time of 2014-09-01 01:17:35 would have a date_minute15 of 2014-09-01 01:15:00.

Time zones and `convert_tz`

In general, time computations (differences, durations, and so on) only work correctly when you operate on time values that are all converted to the same time zone, so it is important to keep time zones in mind when writing LookML.

Looker has various time zone settings that convert time-based data between different time zones. Looker converts time zones by default. If you don't want Looker to perform a time zone conversion for a particular dimension or dimension group, you can use the convert_tz parameter described on the convert_tz parameter documentation page.

Dialect support for milliseconds and microseconds

Looker supports precision to microseconds; however, some databases support precision only to seconds. If a database encounters a time type more precise than it can support, it will round up to seconds.

In the latest release of Looker, the following dialects support milliseconds:

Dialect	Supported?
Actian Avalanche	Yes
Amazon Athena	Yes
Amazon Aurora MySQL	Yes
Amazon Redshift	Yes
Apache Druid	Yes
Apache Druid 0.13+	Yes
Apache Druid 0.18+	Yes
Apache Hive 2.3+	Yes
Apache Hive 3.1.2+	Yes
Apache Spark 3+	Yes
ClickHouse	No
Cloudera Impala 3.1+	Yes
Cloudera Impala 3.1+ with Native Driver	Yes
Cloudera Impala with Native Driver	Yes
DataVirtuality	Yes
Databricks	Yes
Denodo 7	No
Denodo 8	No
Dremio	Yes
Dremio 11+	Yes
Exasol	Yes
Firebolt	No
Google BigQuery Legacy SQL	Yes
Google BigQuery Standard SQL	Yes
Google Cloud PostgreSQL	Yes
Google Cloud SQL	Yes
Google Spanner	No
Greenplum	Yes
HyperSQL	Yes
IBM Netezza	Yes
MariaDB	Yes
Microsoft Azure PostgreSQL	Yes
Microsoft Azure SQL Database	Yes
Microsoft Azure Synapse Analytics	Yes
Microsoft SQL Server 2008+	Yes
Microsoft SQL Server 2012+	Yes
Microsoft SQL Server 2016	Yes
Microsoft SQL Server 2017+	Yes
MongoBI	No
MySQL	Yes
MySQL 8.0.12+	Yes
Oracle	Yes
Oracle ADWC	Yes
PostgreSQL 9.5+	Yes
PostgreSQL pre-9.5	Yes
PrestoDB	Yes
PrestoSQL	Yes
SAP HANA 2+	Yes
SingleStore	Yes
SingleStore 7+	Yes
Snowflake	Yes
Teradata	Yes
Trino	Yes
Vector	Yes
Vertica	Yes

In the latest release of Looker, the following dialects support microseconds:

Dialect	Supported?
Actian Avalanche	Yes
Amazon Athena	No
Amazon Aurora MySQL	Yes
Amazon Redshift	Yes
Apache Druid	No
Apache Druid 0.13+	No
Apache Druid 0.18+	No
Apache Hive 2.3+	Yes
Apache Hive 3.1.2+	Yes
Apache Spark 3+	Yes
ClickHouse	No
Cloudera Impala 3.1+	Yes
Cloudera Impala 3.1+ with Native Driver	Yes
Cloudera Impala with Native Driver	Yes
DataVirtuality	Yes
Databricks	Yes
Denodo 7	No
Denodo 8	No
Dremio	No
Dremio 11+	No
Exasol	No
Firebolt	No
Google BigQuery Legacy SQL	Yes
Google BigQuery Standard SQL	Yes
Google Cloud PostgreSQL	Yes
Google Cloud SQL	Yes
Google Spanner	No
Greenplum	Yes
HyperSQL	Yes
IBM Netezza	Yes
MariaDB	Yes
Microsoft Azure PostgreSQL	Yes
Microsoft Azure SQL Database	Yes
Microsoft Azure Synapse Analytics	Yes
Microsoft SQL Server 2008+	Yes
Microsoft SQL Server 2012+	Yes
Microsoft SQL Server 2016	Yes
Microsoft SQL Server 2017+	Yes
MongoBI	No
MySQL	Yes
MySQL 8.0.12+	Yes
Oracle	Yes
Oracle ADWC	Yes
PostgreSQL 9.5+	Yes
PostgreSQL pre-9.5	Yes
PrestoDB	No
PrestoSQL	No
SAP HANA 2+	Yes
SingleStore	Yes
SingleStore 7+	Yes
Snowflake	Yes
Teradata	Yes
Trino	No
Vector	Yes
Vertica	Yes

Individual duration types

Typically, durations are handled as a dimension_group that uses type: duration.

It is possible to create one dimension for each individual duration that you want to include, instead of generating all of them in a single dimension_group. This is generally avoided, unless you want to change the Looker timeframe naming convention (such as having a field named Number of Days to Delivery instead of Duration to Delivery).

Several individual duration types are listed in Individual duration types.

When you use a duration type for a dimension, you must also include the sql_start and sql_end parameters to provide the start and end times for calculating the time difference.

The sql_start and sql_end parameters can take any valid SQL expression that contains data in a timestamp, datetime, date, epoch, or yyyymmdd format. The sql_start and sql_end fields can be any of the following:

A reference to a raw timeframe from an existing dimension group of type: time.
A reference to a dimension of type: date_raw.
A SQL expression that is a timestamp, such as a reference to a SQL column that is a timestamp.
A SQL expression that pulls a time from your database, using the appropriate expression for your dialect.

As an example, for this dimension_group definition:

dimension_group: to_delivery {
  type: duration
  intervals: [day, hour]
  sql_start: ${created_raw} ;;
  sql_end: ${delivered_raw};;
}

You can use these dimension parameters as a logical equivalent:

dimension: number_of_days_to_delivery {
  type: duration_day
  sql_start: ${created_raw} ;;
  sql_end: ${delivered_raw};;
}

dimension: number_of_hours_to_delivery {
  type: duration_hour
  sql_start: ${created_raw} ;;
  sql_end: ${delivered_raw};;
}

In the Explore UI, this would create dimensions called Number of Days to Delivery and Number of Hours to Delivery.

Available duration types

The following types are used in the type parameter of an individual dimension to create duration based fields. Do not use these types with the intervals parameter, which is documented on the dimension_group documentation page.

All individual duration types require a timestamp as input from your database.

Type	Description	Example Output
`duration_day`	Calculates a time difference in days	`9 days`
`duration_hour`	Calculates a time difference in hours	`171 hours`
`duration_minute`	Calculates a time difference in minutes	`10,305 minutes`
`duration_month`	Calculates a time difference in months	`3 months`
`duration_quarter`	Calculates a time difference in quarters of the year	`2 quarters`
`duration_second`	Calculates a time difference in seconds	`606,770 seconds`
`duration_week`	Calculates a time difference in weeks	`6 weeks`
`duration_year`	Calculates a time difference in years	`2 years`

Dimension, filter, and parameter types

Usage

Type definitions

bin

distance

duration

location

Supported database dialects for location

number

string

tier

style

classic

interval

integer

relational

Things to consider

time

unquoted

yesno

zipcode

Individual time and date types

Available time-based types

Special types

Time types

Date types

Week types

Month types

Quarter types

Year types

Using date_hourX

Using date_minuteX

Time zones and convert_tz

Dialect support for milliseconds and microseconds

Individual duration types

Available duration types

`bin`

`distance`

`duration`

`location`

Supported database dialects for `location`

`number`

`string`

`tier`

`style`

`classic`

`interval`

`integer`

`relational`

`time`

`unquoted`

`yesno`

`zipcode`

Using `date_hourX`

Using `date_minuteX`

Time zones and `convert_tz`