Google BigQuery


This document describes how to use the TIMESTAMP data type.



BigQuery has the ability to import timestamp data. Once imported, BigQuery displays timestamp data as a UTC string in the command line and browser tools or as a decimal number of seconds since the epoch in the API. You can query timestamp data, perform casting operations, grab the current timestamp, and query certain parts of a timestamp (such as the year, date, minutes, seconds, and so on).

Specifying timestamp Data

To declare a column of type TIMESTAMP, define the following in your schema:


For example, a table may have the following schema:

name:string, age:integer, birthday:timestamp

The actual data may look like this:

john,23,1989-10-02 05:23:48

The TIMESTAMP data type is particular useful for log analysis, where you can import your log data into BigQuery, and query your data using timestamp information. When setting a TIMESTAMP data type, you can choose between two formats:

  • A decimal number specifying the number of seconds since the epoch.

    This number may have decimal places to indicate fractions of a second, to a microsecond precision (six decimal places are preserved). Negative numbers mean dates that are before 1970. For example:


  • A calendar date in the format YYYY-MM-DD HH:MM:SS.

    All calendar dates are interpreted as UTC unless there is an explicit timezone offset, such as:

    2012-06-24 19:06:56 -07:00

    Currently, most non-numeric specifiers, with the exception of "UTC" and "Z", are not supported. Fractional seconds are supported in this format:

    2012-06-24 19:06:56.123456

Querying TIMESTAMP data

You can query TIMESTAMP fields like other regular fields and the resulting column retains its TIMESTAMP type. For example:

$ bq query "SELECT name, birthday FROM dataset.table"
Waiting on job_05a2dfefcbb948759c1aa53e2ae13f3b ... (0s) Current status: DONE
| name |      birthday       |
| john | 1989-10-02 05:23:48 |
| kim  | 1958-06-24 12:18:35 |
| sara | 1988-08-15 19:06:56 |

You can also use comparison predicates between TIMESTAMP fields or with TIMESTAMP literals. Acceptable TIMESTAMP literals are the calendar date or the number of seconds since the epoch:

$ bq query "SELECT name, birthday FROM dataset.table WHERE birthday <= '1959-01-01 01:02:05'"
Waiting on job_6262ac3ea9f34a2e9382840ee11538ef ... (0s) Current status: DONE
| name |      birthday       |
| kim  | 1958-06-24 12:18:35 |

TIMESTAMP functions

For a full list of TIMESTAMP functions, see Date and time functions.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.