This document describes how to use the TIMESTAMP data type.

Contents

• Overview
• Specifying timestamp Data
• Querying timestamp Data

Overview

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:

<column-name>:timestamp

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
kim,54,1958-06-24T12:18:35.5803
sara,24,1988-08-15T19:06:56.235

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:

  john,23,6.23309028E8
  kim,54,-3.636132844197E8
  sara,24,5.87675216235E8

• 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 .

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License , and code samples are licensed under the Apache 2.0 License . For details, see our Site Policies .