Google BigQuery

Using TIMESTAMP

This document describes how to use the TIMESTAMP data type.

Contents

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.

Authentication required

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

Signing you in...

Google Developers needs your permission to do that.