Querying Date-Partitioned Tables

This page describes how to limit the number of partitions scanned when querying a date-partitioned table.

Before you begin

You must first create a date-partitioned table. The _PARTITIONTIME pseudo column is available only in tables that are created as partitioned tables. To create a date-partitioned table, see the example in Creating and Updating Date-Partitioned Tables.

Limiting the number of partitions queried

Use the _PARTITIONTIME pseudo column to limit the number of partitions scanned during a query. For example, the following query scans only the partitions between the dates January 1, 2016 and January 2, 2016 from the partitioned table named temps:

# Query works in legacy and standard SQL
SELECT
  temp
FROM
  mydataset.temps
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
  AND TIMESTAMP('2016-01-02');

In legacy SQL, the _PARTITIONTIME filter works only when the filter is specified as closely as possible to the table name. For example, the following query scans all partitions in table1 despite the presence of the _PARTITIONTIME filter:

# Scans all partitions on t1
SELECT
  t1.field1,
  t2.field1
FROM
  mydataset.table2 t1
CROSS JOIN
  mydataset.table2 t2
WHERE
  t1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field2 = "one"

To effectively use the _PARTITIONTIME filter to limit the number of partitions scanned, use a subquery on table1 with the filter pushed into the subquery, as shown in the following query.

# Scans only the specified partition
SELECT
  t1.field1,
  t2.field1
FROM (
  SELECT
    field1,
    field2
  FROM
    mydataset.table1
  WHERE
    _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
  mydataset.table2 t2
WHERE
  t1.field2 = "one"

Do not include any other columns in the _PARTITIONTIME filter. For example, the following query does not limit the partitions scanned because field2 is a column in the table and BigQuery cannot determine in advance which partitions to select.

# Scans all partitions on table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME + field2 = TIMESTAMP('2016-03-28');

Next steps

• To create and update partitioned tables, see Creating and Updating Date-Partitioned Tables.
• For conceptual information about partitioned tables, see Partitioned Tables.
• For information about _PARTITIONTIME, see The _PARTITIONTIME pseudo column.
• For partitioned tables best practices, see Partitioned Tables Best Practices.