BigQuery - Datetime vs Timestamp

google-bigquery bigquery-standard-sql
NirKa picture NirKa · Dec 8, 2017 · Viewed 9.6k times · Source

I looked on the documentation for google big query data types, checking the differences between TimeStamp to Datetime data types.

As I understand the main difference is:

Unlike Timestamps, a DATETIME object does not refer to an absolute instance in time. Instead, it is the civil time, or the time that a user would see on a watch or calendar.

So when should I use Timestamp/Datetime?

Thanks

Answer

Tim Swast picture Tim Swast · Dec 9, 2017

In most cases you will want to use the timestamp data type. It refers to an absolute point in time. BigQuery interprets any timezone information and represents the time internally as a UTC timestamp.

Very rarely would you use a datetime data type, which is a date and a time but no time zone. The example I like to give is that you'd use a datetime to represent pi day, 2017, since it occurs at 2017-03-14 15:09:26.535898 in each time zone separately.

Related questions

Computing Percentiles In BigQuery

I am using BigQuery, and I need to compute the 25th, 50th, and 75th percentile of a column of a dataset. For example, how can I get the aforementioned numbers using BigQuery and STANDARD SQL. I have looked at the …

Read More
efficient way to compare two tables in bigquery

I am interested in comparing, whether two tables contain the same data. I could do it like this: #standardSQL SELECT key1, key2 FROM ( SELECT table1.key1, table1.key2, table1.column1 - table2.column1 as col1, table1.col2 - table2.col2 …

Read More
Setting GOOGLE_APPLICATION_CREDENTIALS for BigQuery Python CLI

I'm trying to connect to Google BigQuery through the BigQuery API, using Python. I'm following this page here: https://cloud.google.com/bigquery/bigquery-api-quickstart My code is as follows: import os import argparse from apiclient.discovery import build from apiclient.…

Read More