Stay organized with collections
Save and categorize content based on your preferences.
Table decorators in legacy SQL
Normally, BigQuery performs a full column scan when
running a query.
You can use table decorators in legacy SQL to perform a more cost-effective query of a
subset of your data. Table decorators can be used whenever a table is read,
such as when copying a table,
exporting a table,
or listing data using tabledata.list.
Table decorators support relative and absolute <time> values. Relative
values are indicated by a negative number, and absolute
values are indicated by a positive number. For example, -3600000 indicates one
hour ago in milliseconds, relative to the current time; 3600000
indicates one hour in milliseconds after 1/1/1970.
Time decorators
Time decorators (formerly known as snapshot decorators) reference a table's
historical data at a point in time.
Syntax
@<time>
References a table's historical data at <time>,
in milliseconds since the epoch.
<time> must be within the last seven days and
greater than or equal to the table's creation time, but less than the table's
deletion or expiration time.
@0 is a special case that references the oldest data available for the
table.
Time decorators are also used outside of legacy SQL. You can use them in the
bq cp command to
restore deleted tables
within seven days of table deletion.
Examples
To get the historical data for a table at one hour ago:
Relative value example
#legacySQL
SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000]
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-25 UTC."],[[["\u003cp\u003eTable decorators in legacy SQL allow for cost-effective querying of data subsets, as opposed to full column scans, and can be used when copying, exporting, or listing table data.\u003c/p\u003e\n"],["\u003cp\u003eTime decorators, indicated by \u003ccode\u003e@<time>\u003c/code\u003e, reference a table's historical data at a specific point in time, measured in milliseconds since the epoch and within the last seven days.\u003c/p\u003e\n"],["\u003cp\u003eRange decorators, using the \u003ccode\u003e@<time1>-<time2>\u003c/code\u003e syntax, specify a range of time to target data added between the two times, with the second time defaulting to "now" if not specified.\u003c/p\u003e\n"],["\u003cp\u003eTime decorators can also be used in the \u003ccode\u003ebq cp\u003c/code\u003e command to restore tables deleted within seven days, expanding beyond their usage just within SQL queries.\u003c/p\u003e\n"],["\u003cp\u003eGoogleSQL's \u003ccode\u003eFOR SYSTEM_TIME AS OF\u003c/code\u003e clause and time-partitioned tables offer comparable functionality to time and range decorators, respectively, making table decorators not available in GoogleSQL.\u003c/p\u003e\n"]]],[],null,["# Table decorators in legacy SQL\n==============================\n\n| **Caution:** This document describes table decorators in legacy SQL query syntax. The preferred query syntax for BigQuery is GoogleSQL. Standard SQL does not support table decorators, but the [`FOR SYSTEM_TIME AS OF`](/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of) clause in GoogleSQL provides functionality equivalent to time decorators. For range decorators, you can achieve similar semantics in GoogleSQL by using time-partitioned tables. For more information, see [Table decorators](/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#table_decorators) in the GoogleSQL migration guide.\n\nNormally, BigQuery performs a full column scan when\n[running a query](https://cloud.google.com/bigquery#querying-data).\nYou can use table decorators in legacy SQL to perform a more cost-effective query of a\nsubset of your data. Table decorators can be used whenever a table is read,\nsuch as when copying a table,\n[exporting a table](https://cloud.google.com/bigquery/exporting-data-from-bigquery),\nor listing data using `tabledata.list`.\n| **Note:** Range decorators aren't supported in GoogleSQL. To view the status of this feature request, see the [BigQuery feature request tracker](https://issuetracker.google.com/issues/35905931). You can click the **Vote for this issue and get email notifications** icon (the star) to register your support for the feature.\n\nTable decorators support relative and absolute `\u003ctime\u003e` values. Relative\nvalues are indicated by a negative number, and absolute\nvalues are indicated by a positive number. For example, `-3600000` indicates one\nhour ago in milliseconds, relative to the current time; `3600000`\nindicates one hour in milliseconds after 1/1/1970.\n\nTime decorators\n---------------\n\nTime decorators (formerly known as *snapshot decorators*) reference a table's\nhistorical data at a point in time.\n\n### Syntax\n\n```\n@\u003ctime\u003e\n```\n\n- References a table's historical data at *\u003ctime\u003e*, in milliseconds since the epoch.\n- *\u003ctime\u003e* must be within the last seven days and greater than or equal to the table's creation time, but less than the table's deletion or expiration time.\n- `@0` is a special case that references the oldest data available for the table.\n\nTime decorators are also used outside of legacy SQL. You can use them in the\n[`bq cp` command](/bigquery/docs/reference/bq-cli-reference#bq_cp) to\n[restore deleted tables](/bigquery/docs/restore-deleted-tables)\nwithin seven days of table deletion.\n\n### Examples\n\nTo get the historical data for a table at one hour ago:\n\n**Relative value example** \n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000]\n\n**Absolute value example**\n\n1. Get *\u003ctime\u003e* for one hour ago:\n\n #legacySQL\n SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)\n\n2. Then, replace *\u003ctime\u003e* in the following query:\n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@time]\n\nRange decorators\n----------------\n\n### Syntax\n\n```\n@\u003ctime1\u003e-\u003ctime2\u003e\n```\n\n- References table data added between *\u003ctime1\u003e* and *\u003ctime2\u003e*, in milliseconds since the epoch.\n- *\u003ctime1\u003e* and *\u003ctime2\u003e* must be within the last seven days.\n- *\u003ctime2\u003e* is optional and defaults to 'now'.\n\n### Examples\n\n**Relative value examples**\n\nTo get table data added between one hour and half an hour ago: \n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000--1800000]\n\nTo get data from the last 10 minutes: \n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-600000-]\n\n**Absolute value example**\n\nTo get table data added between one hour and half an hour ago:\n\n1. Get *\u003ctime1\u003e* for one hour ago:\n\n #legacySQL\n SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)\n\n2. Get *\u003ctime2\u003e* for a half hour ago:\n\n #legacySQL\n SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -30, 'MINUTE')/1000)\n\n3. Replace *\u003ctime1\u003e* and\n *\u003ctime2\u003e* in the following query:\n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@time1-time2]"]]
